Get values of trellis

[Anonymous]

Hello everyone,

I have a Bar Chart which uses two dimensions : Month and Product. I use the trellis option to split my graph by month.

I need in my expression a ratio between the current month (value in the trellis) and the previous month. Consequently, I need to use a set Analysis which takes the value of the month in the trellis:

Sum({MonthTrellis} Sales) / Sum({MonthTrellis-1} Sales)

How should I do this expression ?

Thanks in advance for your help,

Quentin

[yduval75]

You can try this expression.

Sum({<MonthTrellis={'$(=Max(MonthTrellis))'}>} Sales) / Sum(MonthTrellis={'$(=Max(MonthTrellis)-1)'}>} Sales)

[Anonymous]

Hi Yoann,

Thanks for your quick answer.

I tried the expression you give to me, but it doesn't work for my chart. It takes only the max value of my application not all the values of the Month field in dimension.

Thanks,

Quentin

[Anonymous]

Does anyone have an idea about this problem?

Thanks in advance,

Quentin

[mato32188]

Dunno hows your month's field set...

let vMonth = month(DateField)

let vPrMonth = month(DateField)-1

...

Sum( {<DateField = {$(vMonth)}>} Sales) / Sum( {<DateField = {$(vPrMonth)}>} Sales)

[Anonymous]

Hi Martin,

Thanks for your answer.

My Month field is set like this : 01, 02, 03 ... 12

I already tried with variables but it's not working.

This is my chart :

   -   Dimensions : Month => Use as Trellis to split.

                           Product => Main dimension in my bar chart

   -   Expressions : Sum(Sales)

For example, in the Trellis 02, I want to have :

     Sum({$<Year={CurrentYear}, Month={'02'}>}Sales) / Sum({$<Year={CurrentYear}, Month={'01'}>}Sales)

And for the trellis 01 :

     Sum({$<Year={PreviousYear}, Month={'01'}>}Sales) / Sum({$<Year={PreviousYear}, Month={'12'}>}Sales)

But, it will do it automatically for each possible month in my chart.

Thanks in advance,

Quentin

[#7]

Have you tried using above()?

As such:

Sum(Sales) / above(Sum(Sales))

[Anonymous]

Hello,

Thanks for your answer.

Yes I already tried the above() function but it seems that it calculates data of the previous product and not the previous month.

Regards,

Quentin