topic Re: Adding total expression to pivot with horizontal dimension in QlikView

Adding total expression to pivot with horizontal dimension

dafnis14 — Sat, 08 Sep 2018 06:32:40 GMT

Hi,

I have a pivot table with months as horizontal dimension.

I need to add totals expressions with some set analysis, which should appear only at the end (for all months only).

currently they appear for each month.

Attached is the expected report format.

The yellow represents actual value. The grey - forecast values.

Please note that the Total Actual includes actual month values only.

Would appreciate suggestions how to do it.

Thanks!

Re: Adding total expression to pivot with horizontal dimension

petter — Sat, 08 Sep 2018 06:35:52 GMT

If you have a sample app with some sample data it would be so much quicker to just show you.

Re: Adding total expression to pivot with horizontal dimension

Gopi_E — Sat, 08 Sep 2018 06:38:22 GMT

Hi Dafnis,

Can you elaborate exactly what you want to display in your pivot.

Re: Adding total expression to pivot with horizontal dimension

petter — Sat, 08 Sep 2018 06:47:04 GMT

Here you can see how you can use the [Presentation] tab and enble the [Show Partial Sums] for the dimension [Month] and get the sum for all months on the rightmost column in the pivot table:

Re: Adding total expression to pivot with horizontal dimension

dafnis14 — Sat, 08 Sep 2018 06:59:36 GMT

Hi,

I edited the post. hope it's clearer.

Thanks!

Re: Adding total expression to pivot with horizontal dimension

dafnis14 — Sat, 08 Sep 2018 07:00:08 GMT

Hi,

Please see the update to my post.

Thanks!

Re: Adding total expression to pivot with horizontal dimension

petter — Sat, 08 Sep 2018 12:29:40 GMT

The answer is almost exactly the same.

You can have an expression like this if the Actual and Forecast are in separate fields:

If( Month > ActualMonth , Sum(Forecast) , Sum(Actual) )

Or if you have the forecast and actual in the same field but have a dimension indicating the type of values:

If( Month > ActualMonth, Sum({<Type={'Forecast'}>} Sales) , Sum( {<Type={'Actual'}>} Sales ) )

Re: Adding total expression to pivot with horizontal dimension

dafnis14 — Sat, 08 Sep 2018 12:58:29 GMT

Hi Petter,

Thanks for helping me...

I don't quite understand your solution.

There is only one field: Month. (no ActualMonth). I do have a dim for Type.

Re: Adding total expression to pivot with horizontal dimension

dafnis14 — Sat, 08 Sep 2018 13:31:41 GMT

Hi Petter,

I think the way to achieve this is using SecondaryDimensionality.

The second dimension... or how to use secondarydimensionality()

if(SecondaryDimensionality() =0, Sum( {<Type={'Actual'}>} Sales ) , Sum(Sales )

Thanks for giving me the direction...

Re: Adding total expression to pivot with horizontal dimension

petter — Sat, 08 Sep 2018 13:34:05 GMT

yes that should work too...

Re: Adding total expression to pivot with horizontal dimension

dafnis14 — Sat, 08 Sep 2018 13:55:52 GMT

If i may..

Is there a way to add an expression that would show only as a Total?

Currently, it is added for each month.

Seems that for such a case, the HorizontalDimensionality doesn't work.

Thanks!