Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mrthomasshelby
Creator III
Creator III

Subtotals for a calculated column

Hello,

I have a column in my table calculated with an 'if clause' as shown in the image:

Snip1.jpg

Basically the field 'Normalized Rec Cost1' is calculated on the logic that if the Equipment is the same, it's the difference of the previous Rec Cost1 and the current Rec Cost1. Now my issue with this is that the subtotal for the field 'Normalized Rec Cost1' is showing up as the same as 'Rec Cost1', which is incorrect. How to get the correct subtotal for the field 'Normalized Rec Cost1'?

I tried using a simple Sum(Aggr()) infront of my if clause but that doesn't seem to work and changes the Normalized Rec Cost1 field to wrong values. Help me get the correct subtotal for the field 'Normalized Rec Cost1' please. Thanks in advance!

 

Attaching the qvf for your reference. 

 

@tresesco @sunny_talwar 

Labels (3)
1 Solution

Accepted Solutions
sunny_talwar

Try this

=Sum(Aggr(
If([Equipment] = Above(TOTAL [Equipment]), (Sum(Aggr(A1, [Sl No])) - Above(TOTAL Sum(Aggr(A1,[Sl No])))),
Sum(Aggr(A1,[Sl No])))
, Equipment, ([Start Date], (NUMERIC)), [Sl No], Unit, Field1, [End Date]))

View solution in original post

2 Replies
sunny_talwar

Try this

=Sum(Aggr(
If([Equipment] = Above(TOTAL [Equipment]), (Sum(Aggr(A1, [Sl No])) - Above(TOTAL Sum(Aggr(A1,[Sl No])))),
Sum(Aggr(A1,[Sl No])))
, Equipment, ([Start Date], (NUMERIC)), [Sl No], Unit, Field1, [End Date]))
mrthomasshelby
Creator III
Creator III
Author

Thanks a lot Sunny! It works now.