topic Re: Pivot Calculation Problem in QlikView

Pivot Calculation Problem

Anil_Babu_Samineni — Wed, 25 Nov 2020 16:16:04 GMT

Dear Friends,

Why this pivot table getting problem

Please check the calculation? Sum. Left of everything is working but this is not working

Correct Value is - 30, But here showing 29.

Can i know what is modification i required

Re: Pivot Calculation Problem

Thu, 21 Jul 2016 09:38:06 GMT

please share the expression. this may be happening because of DISTINCT clause in your expression

Re: Pivot Calculation Problem

qlikview979 — Thu, 21 Jul 2016 09:38:29 GMT

Hi Anil,

Can you share your file

Regards

Re: Pivot Calculation Problem

tresB — Thu, 21 Jul 2016 09:40:29 GMT

What is the expression you are using for that column? What is the dimension?

Re: Pivot Calculation Problem

antoniotiman — Thu, 21 Jul 2016 09:41:32 GMT

Do You have some rounding ?

Re: Pivot Calculation Problem

Anonymous — Thu, 21 Jul 2016 09:43:03 GMT

Hi Anil

What number format are you using? It looks possible it could be a rounding issue as it seems strange the other columns add correctly. Can you share your app or expression please.

Kindest Regards

Re: Pivot Calculation Problem

narendiran — Thu, 21 Jul 2016 09:44:45 GMT

Hi Anil,

Could you supply few more details, please.

To do cross check you can also check the same with straight table or table.

Re: Pivot Calculation Problem

awhitfield — Thu, 21 Jul 2016 09:52:18 GMT

Hi Anil,

please upload your qvw, the right hand column doesn't add up either, it should be 45!

Andy

Re: Pivot Calculation Problem

qlikview979 — Thu, 21 Jul 2016 09:56:27 GMT

Hi Anil,

I Think

Right side last column also showing wrong count 41 ,actual count is 45 please up lode your file.

Regards

Re: Pivot Calculation Problem

Anil_Babu_Samineni — Thu, 21 Jul 2016 10:07:44 GMT

Hey,

Here i am using Two dimensions one C_Description, Which is has 4 names and

P_Des which is has 4 names

Expression for week - =sum({<C_DESCRIPTION={'Reportable','Recordable','Restricted work Cases','Lost Work Cases'} >} S_COUNT)

For YTD - =sum({<[Master Month]={">=1 <=$(vMaxMonth)"},C_DESCRIPTION={'Reportable','Recordable','Restricted work Cases','Lost Work Cases'}>} S_COUNT)

Re: Pivot Calculation Problem

PrashantSangle — Thu, 21 Jul 2016 10:13:35 GMT

Hi,

try with aggr()

sum(Aggr(YourExpression,dim1,dim2))

like for Week

sum(Aggr(sum({<C_DESCRIPTION={'Reportable','Recordable','Restricted work Cases','Lost Work Cases'} >} S_COUNT),C_DESCRIPTION,dim2))

Regards

Re: Pivot Calculation Problem

Anil_Babu_Samineni — Thu, 21 Jul 2016 10:19:09 GMT

My second dimension is Calculated dimension, =if(wildmatch(P_Des,'All Plants')=0,P_Des)

If i apply your expression, Nothing 0 Values showing

And i want to ask, Can i know how to calculate YTD?

I am using this for YTD

Sum({<[Master Year]=, [Master Month]=, Quarter=, [Master Week]=, DATE=, Desc={'A','B','C','D'}, DATE={">=$(=Num(YearStart(Max(DATE))))<=$(=Max(DATE))"}>} DISTINCT Sales)

Re: Pivot Calculation Problem

PrashantSangle — Thu, 21 Jul 2016 10:25:16 GMT

Hi,

What you tried???

try below

sum(Aggr(sum({<C_DESCRIPTION={'Reportable','Recordable','Restricted work Cases','Lost Work Cases'} >} S_COUNT),C_DESCRIPTION,P_Des))

or why you required calculated dimension

just add P_Des and expression could be

sum({<C_DESCRIPTION={'Reportable','Recordable','Restricted work Cases','Lost Work Cases'},P_Des-={"All Plants"} >} S_COUNT)

Regards,

Re: Pivot Calculation Problem

Anil_Babu_Samineni — Thu, 21 Jul 2016 10:31:18 GMT

@Max, time god it's working.

But, i want to implement YTD Calculation also?

Re: Pivot Calculation Problem

PrashantSangle — Thu, 21 Jul 2016 10:40:47 GMT

Hi,

do same as whichever solution work?? which work aggr() or adding P_Des-={"All Plants"} in set analysis?

try below

using aggr()

=sum(aggr(sum({<[Master Month]={">=1 <=$(vMaxMonth)"},C_DESCRIPTION={'Reportable','Recordable','Restricted work Cases','Lost Work Cases'}>} S_COUNT),C_DESCRIPTION,P_Des))

sum({<[Master Month]={">=1 <=$(vMaxMonth)"},C_DESCRIPTION={'Reportable','Recordable','Restricted work Cases','Lost Work Cases'},P_Des-={"All Plants"} >} S_COUNT)

I will suggest remove calculated dimension and update set analysis, because calculated dimension is performance killer.

Regards,

Re: Pivot Calculation Problem

Anil_Babu_Samineni — Thu, 21 Jul 2016 10:44:14 GMT

Max, How to test manually is that YTD values are correct or Not?

Re: Pivot Calculation Problem

PrashantSangle — Thu, 21 Jul 2016 10:50:10 GMT

Test in straight table

take date/ month field add expression which you use to calculate YTD value.

Check you get proper value in front of those dates...

also don't give name to expression label. At front end put your mouse pointer on it and check what it is evaluating.

Regards

Re: Pivot Calculation Problem

Anil_Babu_Samineni — Thu, 21 Jul 2016 10:57:49 GMT

Max, Nothing diff there. Because, we just copy that Expression and apply same thing

Can i know how this value coming?

Explain me please?

Re: Pivot Calculation Problem

PrashantSangle — Thu, 21 Jul 2016 11:36:20 GMT

Hi,

that why I tell you to take date dimension. It will give you idea that those dates are within YTD or not.

also In chart when you take your pointer on expression label it will highlight date period which you are comparing.

In your expression it will generate expression for [Master Month]={">=1 <=$(vMaxMonth)"}

Regards,

Re: Pivot Calculation Problem

Anil_Babu_Samineni — Thu, 21 Jul 2016 11:46:56 GMT

Max, Yes i remove that calc dimension and choose normal thing. But Exclude is not working while i am using that

In that expression, What is the happen?

As i understand, here in b/w of 1 and 12 Months data fetching to that field.

So, Can you please explain me how many fields are acting for tSum(his YTD calculation

1) C_DESCRIPTION

2) Sum(S_COUNT) where month in b/w of 1 and 12 (Jan --- Dec)

3) That expression need to show the C_DESCRIPTION values

4) P_DES , Here those two dim are Depends

What is the form of aggr here.