Rolling X Months does not work with value list

[deniscamh]

Hello Community,

I have a variable that creating conditions for rolling x Months

Looks like this:VRollMonth = [Survey Year]=,[Survey Month]=,[Survey Date]= ,[Survey Month Year]={">$(=monthend(AddMonths(Max([Survey Date]),-$(vNumRollinMonth))))<=$(=Max([Survey Date]))"}

When vNumRollinMonth it the number that user can change to choose how many months back they want to go.

Then I create bar chart with [Survey Month Year] as dimension one and Dealer Code as Dimension two.

My expression is like this sum({<$(vRollMonthCSE)>}(msrConvenienceIndexQuestionPoints1)). and this works fine when I choose any Months the chart displays all the data by Months based on vNumRollinMonth that user chooses..

But I need to create a chart with second dimension as Value list.

So my chart will be: First dimension: [Survey Month Year] , second Dimension: ValueList('All','Dealer Zone') and the measure is

if(ValueList('All','Dealer Zone')= 'All', sum({<$(vRollMonthCSE)>}(msrConvenienceIndexQuestionPoints1)),

if(ValueList('All','Dealer Zone')= 'Dealer Zone',sum({<$(vRollMonthCSE)>}(msrConvenienceIndexIndexScore)))).

But in that case when I choose a month the chart shows only the selected months and does not show all the other months form the selected back based on vNumRollinMonth .

Any Ideas why?

Please help

[sunny_talwar]

May be create an Island table to do the same thing you are doing with ValueList and then make sure to use the same set analysis in your island field check?

[deniscamh]

Thanks for replay Sunny,

Would you be able to explain what is Island table?

Thanks

[deniscamh]

Ok I figured out what it is.

Unfortunately I cannot separate it from data as the client wants to be able to add any fields the want so it has to come from full data set.

[sunny_talwar]

Island table is a table which isn't connected to the other tables in your database... You can add this table Inline like this

LOAD * INLINE [

ValueListDim

All

Dealer Zone

];

and now add ValueListDim as your second dimension and try this expression

If(Only({<$(vRollMonthCSE)>} ValueListDim) = 'All', Sum({<$(vRollMonthCSE)>}(msrConvenienceIndexQuestionPoints1)),

If(Only({<$(vRollMonthCSE)>} ValueListDim) = 'Dealer Zone', Sum({<$(vRollMonthCSE)>}(msrConvenienceIndexIndexScore)))).

[sunny_talwar]

Not sure I understand what you mean?

[deniscamh]

ok,

Let me try this.

[deniscamh]

Hi Sunny,

Unfortunately it still showing only selected month when I make the selection.

Any suggestions?

[sunny_talwar · Accepted solution]

How about if you try this

If(Only({<$(vRollMonthCSE)>} TOTAL <ValueListDim> ValueListDim) = 'All', Sum({<$(vRollMonthCSE)>}(msrConvenienceIndexQuestionPoints1)),

If(Only({<$(vRollMonthCSE)>} TOTAL <ValueListDim> ValueListDim) = 'Dealer Zone', Sum({<$(vRollMonthCSE)>}(msrConvenienceIndexIndexScore))))

[deniscamh]

Thanks Sunny,

It works now.

I just need to check if numbers are correct.

Thank you