topic Aggregation in pivot table in QlikView

Aggregation in pivot table

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

Hello Qlik Developers

I have a pivot table in which i have a calculated dimension called

=Num(Aggr(Sum(HCS_Data.Approved_Amount_per_Attendee),HCS_Data.NPI_Number), '#,##0.00').

This is summing up the total amount based on npi. This is working fine when there is a Npi Number but if the Npi number is null its also calculating sum(amount) for all nulls.

My requirement is if i have npi number calculate sum(amount) and if i don't have NPi number display amount. Please find the attached app and also screen shot. Thanks in Advance.

Thanks

Anil

Re: Aggregation in pivot table

Digvijay_Singh — Tue, 17 Jan 2017 23:10:08 GMT

May be something like this -

=if(len(HCS_Data.NPI_Number)>0,

Num(Aggr(Sum(HCS_Data.Approved_Amount_per_Attendee),HCS_Data.NPI_Number), '#,##0.00'),

<Amount Field>)

Cannot see qvw as using personal edition of Qlikview.

Re: Aggregation in pivot table

dandaanilreddy — Tue, 17 Jan 2017 23:27:14 GMT

Hi Vijay

Thanks for the reply. The expression what you provided solve the amount issue but i wrote color condition for the amount like

=If(Sum(TOTAL <HCS_Data.NPI_Number> HCS_Data.Approved_Amount_per_Attendee)>350,rgb(179,0,0),

If(Sum(TOTAL <HCS_Data.NPI_Number> HCS_Data.Approved_Amount_per_Attendee)>325,rgb(255, 0, 0),

If(Sum(TOTAL <HCS_Data.NPI_Number> HCS_Data.Approved_Amount_per_Attendee)>300,rgb(255, 191, 0),

If(Sum(TOTAL <HCS_Data.NPI_Number> HCS_Data.Approved_Amount_per_Attendee)>275,rgb(255, 255, 0),

If(Sum(TOTAL <HCS_Data.NPI_Number> HCS_Data.Approved_Amount_per_Attendee)>0,rgb(255, 255, 255))))))

But the color is not working based on the selection when i select amount greater than 350 the color should be red. But the red color is also appearing for amount like 120, 117. Can you help me with this please?

Thanks

Anil

Re: Aggregation in pivot table

Digvijay_Singh — Wed, 18 Jan 2017 00:36:12 GMT

Can you put these expression in text box and see what value they show when you make selections.

Re: Aggregation in pivot table

dandaanilreddy — Wed, 18 Jan 2017 00:42:44 GMT

What do mean by value for this expression. The expression gives the colors based on range selection right ? I am not sure what you are referring me

Thanks

Anil Danda

Re: Aggregation in pivot table

Digvijay_Singh — Wed, 18 Jan 2017 00:45:12 GMT

I mean the value of Sum(TOTAL <HCS_Data.NPI_Number> HCS_Data.Approved_Amount_per_Attendee)

Re: Aggregation in pivot table

dandaanilreddy — Wed, 18 Jan 2017 00:47:58 GMT

The total amount value is correct but i have an issue with color condition. The above color expression is not working properly for the dimension. Is there any other way to write the color condition?

Thanks

Anil

Re: Aggregation in pivot table

Digvijay_Singh — Wed, 18 Jan 2017 00:57:05 GMT

You may try color expression using the similar expression you used for calculated dimension -

Aggr(Sum(HCS_Data.Approved_Amount_per_Attendee),HCS_Data.NPI_Number)

Since it worked for calculated dim calc, it should work for color exp as well.

I am sorry, cannot see qvw as not having license, may be other experts can help further.

Regards,

Re: Aggregation in pivot table

qlikviewwizard — Wed, 18 Jan 2017 02:39:01 GMT

Hi,

Try like this:

=If(Sum(TOTAL <HCS_Data.NPI_Number> HCS_Data.Approved_Amount_per_Attendee)>350 ,rgb(179,0,0),

If(Sum(TOTAL <HCS_Data.NPI_Number> HCS_Data.Approved_Amount_per_Attendee)>325 and Sum(TOTAL <HCS_Data.NPI_Number> HCS_Data.Approved_Amount_per_Attendee)<349,rgb(255, 0, 0),

If(Sum(TOTAL <HCS_Data.NPI_Number> HCS_Data.Approved_Amount_per_Attendee)>300 and Sum(TOTAL <HCS_Data.NPI_Number> HCS_Data.Approved_Amount_per_Attendee)<324,rgb(255, 191, 0),

If(Sum(TOTAL <HCS_Data.NPI_Number> HCS_Data.Approved_Amount_per_Attendee)>275 and Sum(TOTAL <HCS_Data.NPI_Number> HCS_Data.Approved_Amount_per_Attendee)<1,rgb(255, 255, 0),

If(Sum(TOTAL <HCS_Data.NPI_Number> HCS_Data.Approved_Amount_per_Attendee)>0,rgb(255, 255, 255))))))