topic Re: Set analysis on two dimensions and ignoring selection in App Development

Set analysis on two dimensions and ignoring selection

cmc — Tue, 13 Sep 2022 20:32:13 GMT

Hi,

My data consists of 4 columns: a product_category column, date column, upper date limit, and measure we'll call sales.

Category	Date	date_upper_limit	sales
A	1/1/2021	2/1/2021	3
A	2/1/2021	2/1/2021	4
A	3/1/2021	2/1/2021	5
B	1/1/2021	1/1/2021	33
B	2/1/2021	1/1/2021	44
B	3/1/2021	1/1/2021	55

I'd like to sum the sales where the date is less than or equal to the date upper limit, but also ignoring any selections on the Date dimension.

I currently have the following expression which does everything except ignore the selection on the Date dimension"
sum({$<Date= {"=Date<= date_upper_limit"}>}sales)

Re: Set analysis on two dimensions and ignoring selection

BrunPierre — Tue, 13 Sep 2022 22:10:49 GMT

In this way maybe

sum({$<Date= {"=Date<= date_upper_limit"},Date=>}sales)

Re: Set analysis on two dimensions and ignoring selection

cmc — Tue, 13 Sep 2022 22:27:37 GMT

It doesn't quite work, in this case it will ignore the first condition.

Re: Set analysis on two dimensions and ignoring selection

BrunPierre — Tue, 13 Sep 2022 23:03:21 GMT

Apparently the above expression ignores only the selection in the Date field in my model.

Probably a little change might make it work

=Sum({<Date={"<=$(= Max(date_upper_limit))"},Date=>} sales)

Re: Set analysis on two dimensions and ignoring selection

vinieme12 — Wed, 14 Sep 2022 06:55:34 GMT

As below, inter-record comparison needs be evaluated on a dimension that identifies each row uniquely

to the same example i've added rowID to classify each row uniquely

temp:
load *,recno() as rowID inline [
Cat,Date,date_upper_limit,sales
A,1/1/2021,2/1/2021,3
A,2/1/2021,2/1/2021,4
A,3/1/2021,2/1/2021,5
B,1/1/2021,1/1/2021,33
B,2/1/2021,1/1/2021,44
B,3/1/2021,1/1/2021,55
];

exit Script;

Then in chart use below

sum({<rowID={"=Date<=date_upper_limit"},Date=>}sales)

Re: Set analysis on two dimensions and ignoring selection

cmc — Wed, 14 Sep 2022 13:37:17 GMT

Thanks, but for me this expression was not ignoring selection on the Date dimension

Re: Set analysis on two dimensions and ignoring selection

agigliotti — Wed, 14 Sep 2022 15:19:49 GMT

Hi @cmc ,

I think what suggested by @vinieme12 is going to work properly.
Are you sure the selection is made on the Date field name ?

Re: Set analysis on two dimensions and ignoring selection

cmc — Wed, 14 Sep 2022 15:32:25 GMT

Hello @agigliotti , yes I am selecting the correct field.

Re: Set analysis on two dimensions and ignoring selection

agigliotti — Wed, 14 Sep 2022 15:50:35 GMT

Below some questions to better understand your scenario.

What's the measure expression are you using on your table chart?
Which are the dimensions are you using?
Which values of Date field are selected?

Re: Set analysis on two dimensions and ignoring selection

cmc — Wed, 14 Sep 2022 16:00:55 GMT

Sure, I created a test app with just the data from above:

I selected Date 2/1/2021 , 3/1/2021

As you can see the expression results in value of 4, when I would expect it to show 40. (It's not summing where date == 1/1/2021 since it is not within the Date selection).

Re: Set analysis on two dimensions and ignoring selection

agigliotti — Wed, 14 Sep 2022 18:52:18 GMT

if you remove the Date selections what's the result?

could you provide a sample app with mock data to look at?

Re: Set analysis on two dimensions and ignoring selection

cmc — Wed, 14 Sep 2022 19:31:18 GMT

The result is 40, and sure, I've attached the sample.

Re: Set analysis on two dimensions and ignoring selection

agigliotti — Thu, 15 Sep 2022 07:41:16 GMT

Let's try with the below expression:

sum( {<Date>} if( Date<=date_upper_limit, sales ) )

ELSE if you want to use Set Analysis for better performance you can change the script as below:

temp:
load *,recno() as rowID,
if( Date<=date_upper_limit, 1, 0 ) as flag
inline [
Cat,Date,date_upper_limit,sales
A,1/1/2021,2/1/2021,3
A,2/1/2021,2/1/2021,4
A,3/1/2021,2/1/2021,5
B,1/1/2021,1/1/2021,33
B,2/1/2021,1/1/2021,44
B,3/1/2021,1/1/2021,55
];

using this expression in UI:

sum( {< Date, flag = {'1'} >} sales )

I hope it can helps.

Best Regards

Re: Set analysis on two dimensions and ignoring selection

cmc — Thu, 15 Sep 2022 13:25:59 GMT

Thank you, this worked great!