Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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?

Tags (2)
3 Replies
lironbaram
Honored Contributor II

Re: expression with parameters in a pivot

those month year and month_id are connected

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

MVP
MVP

Re: expression with parameters in a pivot

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

Re: expression with parameters in a pivot

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




Community Browser