Set Analysis Inside Another Set Analysis

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Set Analysis Inside Another Set Analysis

Tue, 24 Jan 2017 02:04:56 GMT

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

topic Re: Set Analysis Inside Another Set Analysis in QlikView

Set Analysis Inside Another Set Analysis

Re: Set Analysis Inside Another Set Analysis