Show all values in Pivot/Straight Table (irrespective of current selection)

[shanky1907]

Hello all,

I have an issue like a have a pivot table which represents data according to three months. Currecnt(whichever is selected), previous month, and previous to previous month.

I have several hierarchy of dimensions. For these three months i have taken an inline field M,M-1,M-2 so accrdingly iahve taken if condition for every expresion.

Now the issue is that the table is showing only those dimension rows which are associated with the month selected i.e. M.

So if we dont have values for M for any expression then it will not show in the pivot chart only instead of that expressions has values for previous or previous to previous month.

So, the sum of Rows is always lesser than expression total for M-1 & M-2.

Please revert to me if any more info you need. Help me with this issue. Any lead will be appreciated.

[Sergey_Shuklin]

Hello!

It would be nice to get a picture or a table of what you have now and what you want to achieve. It's much more easier to understand methods for solving the issue if you see the expected result view.

[shanky1907]

Please find the attached doc

[Sergey_Shuklin]

I guess I've got it. If I understood you correct you use an if-condition in expression. So, when you select something the values appear only for certain periods. Did you use a set analysis? Like sum({<period>}Sales) - it will prevent any selections in "period" field.

[shanky1907]

You got it right to a certain extent. I use If condition but SET expression as well. So the Dimension above the chart M, M-1, M-2 is an Inline field(InlineMonth). M carries the values for the month selected & M-1, M-2 as previous & previous to previous month.

Now, my expression is like this:

If(InlineMonth=M, sum({<currentmonth>}Sales),

If(InlineMonth=M-1,sum({<currentmonth-1>}Sales),

If(InlineMonth=M-2,sum({<currenmonth-2>}Sales)

)))

How can i convert this mix of If and SET into only SET?

[Sergey_Shuklin]

Instead of using InlineMonth as a dimension you can create three different expressions for each month and name them "M", "M-1" and "M-2". It will not change a view of your table and make it period-selection insensitive.

[shanky1907]

OK i understand your point. AS you can see in the attachment, i have multiple expressions. Then the format of the pivot chart will change.