topic Re: Set analysis with selected values in QlikView

Set analysis with selected values

javier_florian — Tue, 03 Jun 2014 22:10:30 GMT

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.

Re: Set analysis with selected values

swuehl — Tue, 03 Jun 2014 22:38:03 GMT

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)

Re: Set analysis with selected values

javier_florian — Tue, 03 Jun 2014 23:03:55 GMT

Hi Swuehl,

When I use that formula, pivot table show:

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

Re: Set analysis with selected values

swuehl — Tue, 03 Jun 2014 23:08:06 GMT

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

Re: Set analysis with selected values

javier_florian — Tue, 03 Jun 2014 23:17:19 GMT

If keep formula:

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

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

Re: Set analysis with selected values

swuehl — Wed, 04 Jun 2014 06:59:17 GMT

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.