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: 
Not applicable

expression with parameters in a pivot

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?

3 Replies
lironbaram
Partner - Master III
Partner - Master III

those month year and month_id are connected

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

jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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