Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cmc
Contributor II
Contributor II

Set analysis on two dimensions and ignoring selection

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)

Labels (3)
1 Solution

Accepted Solutions
agigliotti
Partner - Champion
Partner - Champion

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

View solution in original post

13 Replies
BrunPierre
Master
Master

In this way maybe

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

cmc
Contributor II
Contributor II
Author

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

BrunPierre
Master
Master

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)

 

vinieme12
Champion III
Champion III

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)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
cmc
Contributor II
Contributor II
Author

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

agigliotti
Partner - Champion
Partner - Champion

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 ?

cmc
Contributor II
Contributor II
Author

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

agigliotti
Partner - Champion
Partner - Champion

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?

cmc
Contributor II
Contributor II
Author

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

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

cmc_1-1663171156075.png,

 

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).