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)
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
In this way maybe
sum({$<Date= {"=Date<= date_upper_limit"},Date=>}sales)
It doesn't quite work, in this case it will ignore the first condition.
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)
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)
Thanks, but for me this expression was not ignoring selection on the Date dimension
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 ?
Hello @agigliotti , yes I am selecting the correct field.
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?
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).