topic Re: How to pass Dimension values in Set expression in QlikView

How to pass Dimension values in Set expression

Anonymous — Wed, 31 Jan 2018 06:03:15 GMT

Hi All,

I need to create a chart, where Dimension values needs to be passes inside chart set expression.

Ex: I need sum of sales of those customers which are in previous month , and Trend chart needs to be created with month as dimension

Month ID	Expression
2
3
4	sum( <{customer=p({<Month ID={4}>}customer) * customer=p({<Month ID={3}>}customer) sales)
5	sum( <{customer=p({<Month ID={5}>}customer) * customer=p({<Month ID={4}>}customer) sales)

Please let me know how to pass Month ID in set expression dynamically.

I have tried using max function, but it passes only 5 and 4 in every row.

Regards,

Chaitra

Re: How to pass Dimension values in Set expression

tresB — Wed, 31 Jan 2018 06:12:33 GMT

Set analysis works once for the entire chart. That means, chart row wise evaluation of set analysis would not be that straight forward. I guess the way you might try is: The As-Of Table

Re: How to pass Dimension values in Set expression

sunny_talwar — Wed, 31 Jan 2018 13:00:12 GMT

You can try something like this also (if you have QV12)

Sum(Aggr(If(Customer = Above(Customer), Sum(Sales)), Customer, ([Month ID], (NUMERIC))))

or this if you know that Month ID is correctly sorted in the script

Sum(Aggr(If(Customer = Above(Customer), Sum(Sales)), Customer, [Month ID]))

Re: How to pass Dimension values in Set expression

Anonymous — Thu, 01 Feb 2018 04:39:58 GMT

Hi Sunny,

Thanks for the reply, We have tried approach mentioned by you, Its working 🙂 But we are facing one problem , i.e.,

Requirement is to create trailing line chart , so when any particular month all the 13 previous months til that selected months should be displayed. and also is there any way to write this without aggr as we have larger data set.

Re: How to pass Dimension values in Set expression

sunny_talwar — Thu, 01 Feb 2018 12:41:36 GMT

tresesco‌'s approach of using As-of-Table is the only way to avoid Aggr() .

Requirement is to create trailing line chart , so when any particular month all the 13 previous months til that selected months should be displayed.

Use set analysis for this... have you tried that also? May be share a sample so that we can give you a more precise advice