How to pass Dimension values in Set expression

[Anonymous]

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

[tresesco]

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

[sunny_talwar]

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

[Anonymous]

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.

[sunny_talwar]

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