topic Re: Re: Issue with total column when expression modified as per dimension in QlikView

Issue with total column when expression modified as per dimension

Tue, 18 Mar 2014 08:06:29 GMT

Hello,

I am facing an issue with total column. I have a simple pivot table like this:

Challenge here is, that for current and previous quarters I must multiply result *4, which is very simple, I changed expression to:

=
IF(TextBetween([Qtr],'Q','-') <= Ceil(Month(Today(1)) / 3),
sum(Value)*4,
sum(Value)
)

This shows data fine on changes pivot:

BUT

Total column is wrong, it is showing same value as previously. This is probably connected to my expression for calculationg current and previous quarters (as in total Qtr column does not exist)

I tried working with SecondaryDimensionality, but had no luck.

How can I force Total column to the right to work...

Edit: Attaching sample file.

BR,

Kuba

Re: Issue with total column when expression modified as per dimension

veidlburkhard — Tue, 18 Mar 2014 08:28:37 GMT

Hi saperos_,

you have to use Aggr function to solve this problem.

Please change your expression to

=Sum(Aggr(

IF(TextBetween([Qtr],'Q','-') <= Ceil(Month(Today(1)) / 3),

sum(Value)*4,

sum(Value)

), Division, Location, Qtr))

Enjoy

Burkhard

Re: Issue with total column when expression modified as per dimension

Tue, 18 Mar 2014 08:33:36 GMT

Thank you so much Burkhard! Another solution around AGGR() funditon.

Can you be that kind and explain me how exactly this works? It seems I am unable to understnad when and how to use Aggr() function...

BR,

Kuba

Re: Re: Issue with total column when expression modified as per dimension

veidlburkhard — Tue, 18 Mar 2014 08:41:18 GMT

Hi Kuba,

There is some explanation how to use the Aggr function in the help file. Additionally you should have a look into the attached pdf file.

Hope this helps

Burkhard