Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis Inside Another Set Analysis

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.

1 Reply
Not applicable

Oh the aggr function is quite a beautiful specimen. Figured this one out!