Aggregation in pivot table

[dandaanilreddy]

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

[Digvijay_Singh]

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.

[dandaanilreddy]

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

[Digvijay_Singh]

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

[dandaanilreddy]

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

[Digvijay_Singh]

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

[dandaanilreddy]

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

[Digvijay_Singh]

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,

[qlikviewwizard]

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))))))