Set analysis on two dimensions and ignoring selection

[cmc]

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)

[agigliotti]

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

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

[cmc]

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

[agigliotti · Accepted solution]

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

[cmc]

Thank you, this worked great!