topic Re: Rolling X Months does not work with value list in App Development

Rolling X Months does not work with value list

deniscamh — Wed, 10 Jan 2018 15:16:16 GMT

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

Re: Rolling X Months does not work with value list

sunny_talwar — Wed, 10 Jan 2018 15:22:13 GMT

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?

Re: Rolling X Months does not work with value list

deniscamh — Wed, 10 Jan 2018 15:24:01 GMT

Thanks for replay Sunny,

Would you be able to explain what is Island table?

Thanks

Re: Rolling X Months does not work with value list

deniscamh — Wed, 10 Jan 2018 15:26:27 GMT

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.

Re: Rolling X Months does not work with value list

sunny_talwar — Wed, 10 Jan 2018 15:27:55 GMT

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

Re: Rolling X Months does not work with value list

sunny_talwar — Wed, 10 Jan 2018 15:28:24 GMT

Not sure I understand what you mean?

Re: Rolling X Months does not work with value list

deniscamh — Wed, 10 Jan 2018 15:30:06 GMT

ok,

Let me try this.

Re: Rolling X Months does not work with value list

deniscamh — Wed, 10 Jan 2018 15:53:03 GMT

Hi Sunny,

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

Any suggestions?

Re: Rolling X Months does not work with value list

sunny_talwar — Wed, 10 Jan 2018 15:56:11 GMT

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

Re: Rolling X Months does not work with value list

deniscamh — Wed, 10 Jan 2018 16:00:30 GMT

Thanks Sunny,

It works now.

I just need to check if numbers are correct.

Thank you

Re: Rolling X Months does not work with value list

sunny_talwar — Wed, 10 Jan 2018 16:02:00 GMT

Awesome !!

Please close the thread by marking correct and helpful responses once you have confirmed.

Best,

Sunny

Re: Rolling X Months does not work with value list

deniscamh — Wed, 10 Jan 2018 16:02:32 GMT

Will do

Re: Rolling X Months does not work with value list

deniscamh — Wed, 10 Jan 2018 16:22:42 GMT

Hi Sunny,

Just wanted to thank you for your help.

It is not the first time you are helping me and it always very efficient.

Thank you a lot!!

Re: Rolling X Months does not work with value list

sunny_talwar — Wed, 10 Jan 2018 16:24:34 GMT

No problem at all!! I am glad I am there to help most of the times

Re: Rolling X Months does not work with value list

deniscamh — Wed, 10 Jan 2018 16:43:08 GMT

Thanks Sunny,

I have one more question related to this.

I have added this expression in a color by expression function

if(ValueListDealerZone='All',Red(),blue())

and it works as long as I do not select Month.

When I select Month it turns all to blue except for selected month.

Is there any way to fix that the formula does not reflect the month selection?

Re: Rolling X Months does not work with value list

sunny_talwar — Wed, 10 Jan 2018 16:45:49 GMT

Apply the same logic here as well

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

Re: Rolling X Months does not work with value list

deniscamh — Wed, 10 Jan 2018 16:47:36 GMT

You are rock!

Thank you!!!!!