topic Re: Show all values in Pivot/Straight Table (irrespective of current selection) in QlikView

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

shanky1907 — Wed, 07 Jun 2017 07:13:59 GMT

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.

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

Sergey_Shuklin — Wed, 07 Jun 2017 07:27:23 GMT

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.

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

shanky1907 — Wed, 07 Jun 2017 09:04:22 GMT

Please find the attached doc

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

Sergey_Shuklin — Wed, 07 Jun 2017 16:45:30 GMT

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.

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

shanky1907 — Thu, 08 Jun 2017 04:56:53 GMT

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?

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

Sergey_Shuklin — Thu, 08 Jun 2017 05:33:54 GMT

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.

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

shanky1907 — Thu, 08 Jun 2017 06:33:36 GMT

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.