topic Re: expression with parameters in a pivot in QlikView

expression with parameters in a pivot

Tue, 29 Sep 2015 19:30:49 GMT

In a pivot table I've a dimension field (YEAR_MONTH) and a measure given by an expression with a parameter (MyDim ($1)), I'd like to Pass as parameter a numeric value (MONTH_ID) related to the dimension field, I tought this cold be a solution

$(vMyDim( $(=aggr(only(YEAR_MONTH_ID),YEAR_MONTH))))

but it doesn't work (only works when a single value is selce in YEAR_MONTH)

At The moment I've found a terrible workaround :

if(aggr(only(YEAR_MONTH_ID),YEAR_MONTH)=1,$(vMyDim(1)), if(aggr(only(YEAR_MONTH_ID),YEAR_MONTH)=2,$(vMyDim(2)),0))

Have you any suggestions?

Re: expression with parameters in a pivot

lironbaram — Wed, 30 Sep 2015 07:24:45 GMT

those month year and month_id are connected

is so why don't you just use the field month_id in your expression

Re: expression with parameters in a pivot

jonathandienst — Wed, 30 Sep 2015 07:57:39 GMT

The $ expansions occur before the cubes dimensions are expanded for the pivot table, so you will not be able to use a $ expansion that depends on the dimension values.

Re: expression with parameters in a pivot

Wed, 30 Sep 2015 08:37:06 GMT

the field is YEAR_MONTH_ID not MONTH_ID (sorry for the mistake)

I've changed the workaround now is more simple but still ugly

if(YEAR_MONTH_ID=1,$(vMyDim(1)),

if(YEAR_MONTH_ID=2,$(vMyDim(2)),

0))

this expression only works with a single value selected in YEAR_MONTH, for multiple values in YEAR_MONTH the pivot table has no rows

$(vMyDim( $(=aggr(only(YEAR_MONTH_ID),YEAR_MONTH))))

this expression only returns rows in pivot table but the value of the measure is always the same

$(vMyDim(aggr(only(YEAR_MONTH_ID),YEAR_MONTH)))