Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
if you remove the Date selections what's the result?
could you provide a sample app with mock data to look at?
The result is 40, and sure, I've attached the sample.
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
Thank you, this worked great!