Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a Pivot Table by which two of my dimensions are AGGR equations. I would like to subtract the outputs of these two columns into a third column but am struggling to do so - I'm guessing because of the AGGR().
The first two columns are as follows:
Total Days = Aggr(count(DATE.day),DATE.year)
Days Off = Aggr(sum(TIMEOFF),USER.Id)
Ideas how to subtract the results of these two equations in a third column?
Many thanks,
Mel
Hey,
Try using Num() like:
Num(Aggr(count(DATE.day),DATE.year)) - Num( Aggr(sum(TIMEOFF),USER.Id))
I am not sure whether it works but you can try
Did you try doing something like this for your third expression?
[Total Days] - [Days Off]
where your first two expressions label are Total Days and Days Off.
Best,
S
Thanks, both.
Unfortunately, neither options have been successful so far. The two AGGR() functions are dimensions in my pivot table.
Is there a reason you have them as a dimension and not expression?
Best,
S
Sorted it out in the end, if anyone is trying to do similar:
To subtract two aggr() functions, the entire equation then needs to be wrapped in aggr()
=aggr(
aggr(...)
-
aggr(...)
,FIELD)
Seems to be working - thanks all for your suggestions!
Mel
I would suggest marking your answer as correct so that the thread can be closed.
Best,
S