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: 
javier_florian
Creator III
Creator III

Set analysis with selected values

Hi All,

I have three expression with set analysis to calculate a sales in month selected, budget in month selected and budget in a previous year for month selected, my formula to last expression is:

Sum({1<Field_1={'Criteria'}, YearMonth={$(vPrevMaxYearMonth)}>} Value)

"({1<" take all records...

However, when my user select a product, my set expression doesn't work because pivot table shows all products.

How can fix or improve my set expression if user needs select a year-month, product, business line, and another dimensions?

Thanks in advanced.

5 Replies
swuehl
MVP
MVP

If you want the set expression to be sensitive to user selections, just overriding selections in fields Field_1 and YearMonth, try a set identifier $:

Sum({$<Field_1={'Criteria'}, YearMonth={$(vPrevMaxYearMonth)}>} Value)

javier_florian
Creator III
Creator III
Author

Hi Swuehl,

When I use that formula, pivot table show:

set analysis 2014-Jun.JPG.jpg

My formulas are:

Sales 2014-Apr -> =-(Sum({$<Field_1={'Criteria'}, YearMonth={$(vMaxYearMonth)}>} (Value)))

Budget 2014-Apr -> =-(Sum({$<Field_1={'Criteria'}, YearMonth={$(vMaxYearMonth)}>} (Budget)))

Sales 2013-Apr -> =-Sum({1<Field_1={'Criteria'}, YearMonth={$(vPrevMaxYearMonth)}>} Value)

Regards,

-JFlorian

swuehl
MVP
MVP

Any reason why you still keep the set identifer 1 in the last expression for Sales 2013-Apr?

javier_florian
Creator III
Creator III
Author

If keep formula:

-Sum({$<Field_1={'Criteria'}, YearMonth={$(vPrevMaxYearMonth)}>} Value)

And user select a year-month, I can get any value (result -> 0)

swuehl
MVP
MVP

Ok, if you want to ignore the user's selection in all date fields, like Year and Month, it's not enough to override just YearMonth, you would need to explicitely ignore all the other date fields (assuming Date, Month, Year for now) the user may select in when using the default set identifier:

Sum({$<Field_1={'Criteria'}, YearMonth={$(vPrevMaxYearMonth)}, Date=, Month= , Year= >} Value)

If you only want to regard few fields the user may select, like Product, you can use set identfier 1 and pulling the users selection in:

Sum({1<Field_1={'Criteria'}, YearMonth={$(vPrevMaxYearMonth)}, Product = $::Product>} Value)

or maybe

Sum({1<Field_1={'Criteria'}, YearMonth={$(vPrevMaxYearMonth)}, Product = p() >} Value)

There are differences in last two expressions in the way possible values for field Product (white elements in list box) are handled.