topic Re: Can we Compare row level subtotals in pivot table qlikview in App Development

Can we Compare row level subtotals in pivot table qlikview

gq — Tue, 11 Jul 2023 13:14:16 GMT

I have a requirement to show subtotals for horizontal dimension plan v Actual against column dimensions Year and Month.

Wanted to compare Plan v Actual subtotals for previous month, if variance is greater or less than 0 i need to color the text for that month.

Any help is appreciated.

thanks

Re: Can we Compare row level subtotals in pivot table qlikview

Oleg_Troyansky — Wed, 12 Jul 2023 15:22:55 GMT

Hi there,

I'm pretty sure it's possible to do in QlikView, but it's hard for me to prescribe anything specific without seeing your table and understanding your requirement with more detail.

In a nutshell, you should be working with the "Text Color" attribute of the corresponding Expression (or Dimension?), and you could use the function Dimensionality() to apply the calculation only at the appropriate subtotal level.

Cheers,

Re: Can we Compare row level subtotals in pivot table qlikview

gq — Wed, 12 Jul 2023 15:32:48 GMT

Have attached the image how the table looks like. I have tried with the text expression for the Dimension and Expression. But it is not working as expected.

Re: Can we Compare row level subtotals in pivot table qlikview

marcus_sommer — Thu, 13 Jul 2023 10:12:37 GMT

Take a look on the interrecord-functions above/below/before/after with which each cell of the pivot is accessible from another one. If not a single value should be returned else an array you will need to wrap them within a range-function. If several dimensions are included it could become more complex and you will need TOTAL statements in the functions and maybe a further wrapping with aggr() constructs.

It's not trivial and interrecord-functions and aggr() could decrease the performance by larger data-sets. Therefore I suggest to consider to simplify the object, for example separating the number- and rate-calculation into different views/objects and/or using YYYYMM as dimension instead of applying year and month.

An alternatively would be to remove the horizontal period-dimensions and using n expressions by defining the periods within a set analysis, like:

sum({< RunningPeriod = {"$(=max(RunningPeriod)"}>} Value) -
sum({< RunningPeriod = {"$(=max(RunningPeriod)-1"}>} Value)

Re: Can we Compare row level subtotals in pivot table qlikview

gq — Thu, 13 Jul 2023 18:44:47 GMT

Thank you for your response Marcus!

I have tried Rangesum with before function to compare subtotals for Type 1 and Type 2 in the text expression of Expression and in the dimension 'A' as well.

Its not working as expected, i have also tested just Rangesum expression separately in the pivot table, it works fine

IF(

Rangesum(Count({<A = {'Type1'}>}DISTINCT ID), Before(TOTAL Count(DISTINCT ID), 1, ColumnNo(TOTAL)))
=

Rangesum(Count({<A = {'Type2'}>}DISTINCT ID), Before(TOTAL Count(DISTINCT ID), 1, ColumnNo(TOTAL)))
, Blue(), Green())

Rangesum(Count({<A = {'Type1'}>}DISTINCT ID), Before(TOTAL Count(DISTINCT ID), 1, ColumnNo(TOTAL)))
evaluates to 10
Rangesum(Count({<A = {'Type1'}>}DISTINCT ID), Before(TOTAL Count(DISTINCT ID), 1, ColumnNo(TOTAL)))
evaluates to 10
So i was expecting Blue