7 Replies Latest reply: Apr 10, 2018 5:41 PM by Petter Skjolden

# Nested set analysis

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

StoreDateSales
A4/1/201810
B4/2/201819
A4/2/201812
C4/1/20189
A4/3/201824
B4/3/201821

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

• ###### Re: Nested set analysis

Hi Stephen, would this work instead?

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

• ###### Re: Nested set analysis

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

• ###### Re: Nested set analysis

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

• ###### Re: Nested set analysis

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

• ###### Re: Nested set analysis

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

• ###### Re: Nested set analysis

Date,

Sales,

Store & Date as Store_Date

FROM

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

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

• ###### Re: Nested set analysis

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.