[font="Verdana"]Hi,
If you see the CSV's they dont have a Sale_week column. I am adding it to the query, also the sCountry, sChar (Character language), iYear columns. Each CSV contains 3 yrs (156 weeks) data for a particular Country & Char. So there are 2 CSV's for say, FRANCE viz., DATABASE_FRANCE_EURO.CSV & DATABASE_FRANCE_GLOBAL.CSV. likewise such CSV's for other countries. Also, in each csv fields are same, however Level field contains 2 types of data, Brand level & Item level. Brand level contains consolidated summary totals of Items in each brand. Item level is the detail breakup.
what i am doing is clubbing all the CSV data together using UNION ALL and also adding the above columns to create 2 outputs. The Brand level output - we are only looking at the years, so 3 yrs(156 weeks) as per data (iYear column). The Item level output - we want to fit the 3yrs (156 weeks) data into a 52 week window. The idea is - any Innovation (Description field or SKU) will run only for not more than 52 weeks from the week of launch. So in above SQL code, i am just applying rules:
1] if any Innovation that starts from 1st week of launch and <=52, then it may have had some weeks in previous years. So shift it to column 52-count of sku+1 (Sale_week).
2] else any Innovation that does not start from 1st week of launch and <=52 then just number its column sequentially. (Sale_week).
Exceptions: if you filter on Level='Item', you will see some sku's are having >52 weeks data & 0 & blanks. So i am excluding 0 & blanks & just picking up the maximum sku (<=52).
Outputs: dropdown Filters are on Country Category, Manufacturer, Brand and aggregation done after applying each such filter.
Select:
Your CountrySPAIN
Your CategoryJUICES
Your ManufacturerA.J. BARD
Your BrandRUBICON
Calculations:
Data is grabbed from ItemWeekWise table
Country
Count the number of SKUs in the country with more than 13 weeks of data
For the selected week x compute cumulative values for below KPIs for each of the SKU in the country:
1. Sales Value with Innovation
2. Sales Units with Innovation
3. Weighted Distribution
Based on the above data calculate for each SKU:
4.Values Sales Velocity =Cumulative Sales Value with Innovation/ Cumulative Weighted Distribution
5.Unit Sales Velocity=Cumulative Sales Units with Innovation/ Cumulative Weighted Distribution
"Find the below percentiles for each of the above 5 KPIs
90
50
10"
"Find the percent rank of the below values using the cumulative array for the respective KPI:
Cumulative Value Sales (entered by the user)
Cumulative Unit Sales (entered by the user)
Weighted Distribution (entered by the user)
Values Sales Velocity (entered by the user)
Unit Sales Velocity (entered by the user)"
Category
Count the number of SKUs in the category with more than 13 weeks of data
For the selected week x compute cumulative values for below KPIs for each of the SKU in the category:
1. Sales Value with Innovation
2. Sales Units with Innovation
3. Weighted Distribution
based on the above data calculate for each SKU:
4.Values Sales Velocity =Cumulative Sales Value with Innovation/ Cumulative Weighted Distribution
5.Unit Sales Velocity=Cumulative Sales Units with Innovation/ Cumulative Weighted Distribution
"Find the below percentiles for each of the above 5 KPIs
90
50
10"
"Find the percent rank of the below values using the cumulative array for the respective KPI:
Cumulative Value Sales (entered by the user)
Cumulative Unit Sales (entered by the user)
Weighted Distribution (entered by the user)
Values Sales Velocity (entered by the user)
Unit Sales Velocity (entered by the user)"
Manufacturer
Count the number of SKUs in the manufacturer with more than 13 weeks of data
For the selected week x compute cumulative values for below KPIs for each of the SKU under the manufacturer:
1. Sales Value with Innovation
2. Sales Units with Innovation
3. Weighted Distribution
based on the above data calculate for each SKU:
Values Sales Velocity =Cumulative Sales Value with Innovation/ Cumulative Weighted Distribution
4.Unit Sales Velocity=Cumulative Sales Units with Innovation/ Cumulative Weighted Distribution
"5.Find the below percentiles for each of the above 5 KPIs
90
50
10"
"Find the percent rank of the below values using the cumulative array for the respective KPI:
Cumulative Value Sales (entered by the user)
Cumulative Unit Sales (entered by the user)
Weighted Distribution (entered by the user)
Values Sales Velocity (entered by the user)
Unit Sales Velocity (entered by the user)"
Brand
Count the number of SKUs in the brand with more than 13 weeks of data
For the selected week x compute cumulative values for below KPIs for each of the SKU under the brand:
1. Sales Value with Innovation
2. Sales Units with Innovation
3. Weighted Distribution
based on the above data calculate for each SKU:
4.Values Sales Velocity =Cumulative Sales Value with Innovation/ Cumulative Weighted Distribution
5.Unit Sales Velocity=Cumulative Sales Units with Innovation/ Cumulative Weighted Distribution
"Find the below percentiles for each of the above 5 KPIs
90
50
10"
"Find the percent rank of the below values using the cumulative array for the respective KPI:
Cumulative Value Sales (entered by the user)
Cumulative Unit Sales (entered by the user)
Weighted Distribution (entered by the user)
Values Sales Velocity (entered by the user)
Unit Sales Velocity (entered by the user)"
This is just one output sheet, there are 5 more output sheets. LOL 🙂
Let me know if something is not clear.[/font]