Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Stephen, would this work instead?
SUM(IF(Aggr(sum(sales), store, date)>=20,1,0))
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
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
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
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
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)
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.