topic Re: Challenge in Pivot table in QlikView

Challenge in Pivot table

Nagaraju_KCS — Sat, 14 Aug 2021 18:16:31 GMT

Hi Team,

I have different scenario, trying since one week but I couldn't achieve.

Experts can you please help me!

By default, pivot table is working fine, calculation is ([Amount (USD)]*[Gross BPS])/12*0.5

but my requirement is - let say for Cashflow 5 - May2021 value is 4453 that's fine

but my greater than month May, calculation should be ([Amount (USD)]*[Gross BPS])/12

Screen 1: default pivot table

Screen 2: below screen is my out put

Hope you understand and Thanks in Advance!

Re: Challenge in Pivot table

Kushal_Chawda — Sat, 14 Aug 2021 23:04:48 GMT

@Nagaraju_KCS try below

=if(Before(total Column(1))>0, sum(total <Client>[Amount (USD)])*sum(total <Client>[Gross BPS])/12, sum([Amount (USD)])*sum([Gross BPS])/12*0.5)

Re: Challenge in Pivot table

Nagaraju_KCS — Sun, 15 Aug 2021 01:10:20 GMT

Perfect 😊 - Thank you Kushal

Re: Challenge in Pivot table

Nagaraju_KCS — Mon, 16 Aug 2021 04:11:04 GMT

Hi Kushal,

without any selection working fine, but when user selections values are not showing correct and Months not showing Jan-Dec. few of them months are missing so values mismatching.

and months also not showing all, may be if months show all values will be correct.

can you please help

Re: Challenge in Pivot table

Kushal_Chawda — Mon, 16 Aug 2021 10:56:58 GMT

@Nagaraju_KCS I am not able to open the qvw as running personal edition. What expression are you using?

Re: Challenge in Pivot table

Nagaraju_KCS — Tue, 17 Aug 2021 02:58:24 GMT

Same expression which you were shared

If(Before(total fabs(Column(1)))>0, sum(total <Client>[Amount (USD)])*sum(total <Client>[Gross BPS])/12, sum([Amount (USD)])*sum([Gross BPS])/12*0.5)

I have some negative data also, so I have included fabs function.

@Kushal_Chawda , can you please have a look attached original data

PFA,