rolling 6/4 months in bar /pivot table with multiple or 2 diemsnion

[manoranjan_321988]

Hi Team,

PFA  of my sample data and QVF, I want to show the rolling 6 months data as default with monthyear dim and if there is no data for the rolling months it has to show zero in the bar chart and  if user select any timeframe monthyear  dim has to be changed accordingly as per the selected timeframe example week or year or date, All these are achieved.

now the issue is if i add another dim in the chart and disable the include null value , i am not getting the zero value. bar chart has to  show allways rolling 6 bars, if no data then it shd be zero and similarly pivot shd show the rolling 4 months.

[manoranjan_321988]

@olofmalmstrom  i hope you helped lot on this and  this another scenario, pls help on this

[manoranjan_321988]

@olofmalmstrom  waiting for your response or team waiting response for above issue

[olofmalmstrom]

Hi, Why can't you keep the "Include null values" in the second dimension?

[manoranjan_321988]

sorry for the late reply @olofmalmstrom  , if i keep the include null value then data will show null, now the thing client don't want the null he need it as zero now.

i tried alt in the exp not working, he want to see the rolling 4 months data has to come and then if there is no transaction on that date  then it shd be zero , i also tried pervious of your example like adding sum({1}0) but not working  , but this  sum({1}0) working fine for the bar chart only. with one dim as time frame and multiple exp. if we add multiple dim this sum({1}0) not working in this scenario.

[olofmalmstrom]

Is this what you want ?


In your app I set first dim to exclude null

And in the second Dim I set Include null

Nothing else is changed

/Olof

[manoranjan_321988]

yes but in the pivot table scenario , Could you please disable the null in the timeframe dim  and  enable the null in the other dim in the pivot table chart , it will show rolling rolling 4 months but it will be in null it wont have the zero values, now these null has to be shown as zero in the pivot table for the rolling 4 months data in pivot table chart, this is the issue

[manoranjan_321988]

@olofmalmstrom  waiting for your respnse

[manoranjan_321988]

Hi @olofmalmstrom 

Any updates?

[olofmalmstrom]

The Pivot table is special in how it handles this. If you really want this to work as described, you will need to create a Dummy value for each and every possible combination of the dimensions in the pivot, but this can result in a really large fact table so I would talk to the users if it is really necessary. One workaround could perhaps be to create a straight table with 6 measures, one for each month...