Hello Qlik Community,
I started this project with a lot of hair and have since purchased 3 bottles of Rogaine on Amazon...Please help me stop losing hair!
I have weekly sales data by store location for the previous 2 years. Here is a sample of the data:
Store#, Week, Sales
471, 1/3/2015, $300
2276, 1/3/2015, $700
1314, 1/3/2015, $400
471, 1/2/2016, $500
2276, 1/2/2016, $600
I have two input variables for a user to enter as their desired date ranges.
I want to show the sales for stores that had sales for both the desired date range selected and it's prior year comparable date range
(i.e. 1/2/2016's prior year comparable week is 1/3/2015; basically just -364 from the current year date to get its comparable)
For example, let's say the user selected the lone week of 1/2/2016
I need the table to aggregate the sales for store #s 471 and 2276 since they had sales for both the selected week and its prior year comparable week:
Desired Output:
Current Year Sales: $1,100
Prior Year Sales: $1,000
I have been thinking the best way to do this would be to have a count function in place to count the weeks in both the desired selection and prior year using a set analysis for the prior year data. Then aggregating the sales data for shops that have 2 weeks of sales on record (1 for current and 1 for prior)
My current expression that is not working:
sum(if(count({$<%WeekKey={">=$(=vStartDate)<=$(=vEndDate)"}>}%WeekKey)=13,SalesCY,0))
If you followed all of this, I already applaud you. If you comprehended this and are actually able to help me with what I am trying to accomplish, I will be your best friend until the end of time.
