Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a column in my table calculated with an 'if clause' as shown in the image:
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.
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]))
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]))
Thanks a lot Sunny! It works now.