Nested set analysis

[sparkerb]

Hi everyone,

I am trying to get set analysis working in the following scenario:

Given the following data set, I need to create a line chart that shows the count of stores that went over 20 in sales per day

Store	Date	Sales
A	4/1/2018	10
B	4/2/2018	19
A	4/2/2018	12
C	4/1/2018	9
A	4/3/2018	24
B	4/3/2018	21

So from the above data, I would expect the line chart to show 1 store on the 1st, 0 stores on the 2nd, and 2 stores on the 3rd. However, every equation I have used thus far shows 3 stores every day. Here is the equation I have been working with thus far

count({<store = {"=Aggr(sum(sales), store, date)>=20"}>} distinct store)

While I can't say that the equation above is a good starting point, hopefully it somewhat reflects my intentions. Can anyone help me understand how to get this line chart working properly with a proper equation?

thanks,

Stephen

[rachel_delany]

Hi Stephen, would this work instead?

SUM(IF(Aggr(sum(sales), store, date)>=20,1,0))

[sasiparupudi1]

The below expression needs a unique ID on the left hand side

count({<PrimaryKey= {"=Aggr(sum(sales), store, date)>=20"}>} distinct store)

or else you go with the if condition as Rachel suggested

[sparkerb]

Hi Rachel,

Thanks for the response. I should have also added that I need this to be able to aggregate to other periods as well (i.e. month), and therefore getting the count of distinct stores is an important aspect of what I need. While your suggestion does work for what I originally asked, with my newly added criteria, I need to sort out how to return only distinct stores for a given time period.

thanks,

Stephen

[sparkerb]

Hi Sasidhar,

Thanks for the suggestion. The equation you suggested is what I have been attempting to use, but it does not return any results regardless of the key I use (I have been using store as noted above). Is there a small syntax error int he equation I am not identifying perhaps?

thanks

Stephen

[sasiparupudi1]

Please post a sample app. Also note that set analysis is only calculated once per chart and if you want to have row by row calculation, you will have to use an if condition

[pradosh_thakur]

load script:

LOAD Store,

     Date,

     Sales,

    Store & Date as Store_Date

FROM

[https://community.qlik.com/thread/297646]

(html, codepage is 1252, embedded labels, table is @1);

expression:   COUNT( DISTINCT{<Store_Date={"=Aggr(sum(Sales), Store, Date)>=20"}>} Store)

[petter]

This might be what you are looking for:

     Sum(Aggr( -(Sum(Sales)>=20) , Store , Date ) )

Aggr() should always (or almost always) be wrapped inside an aggregation function as it might return more than 0 or one row. In the case that you get more than one row a measure will not be displayed.