topic Re: Subtracting AGGR equations in QlikView

Subtracting AGGR equations

Wed, 11 Mar 2015 13:26:30 GMT

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

Re: Subtracting AGGR equations

nikhilgarg — Wed, 11 Mar 2015 13:30:48 GMT

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

Re: Subtracting AGGR equations

sunny_talwar — Wed, 11 Mar 2015 13:36:16 GMT

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,

Re: Subtracting AGGR equations

Wed, 11 Mar 2015 14:27:25 GMT

Thanks, both.

Unfortunately, neither options have been successful so far. The two AGGR() functions are dimensions in my pivot table.

Re: Subtracting AGGR equations

sunny_talwar — Wed, 11 Mar 2015 14:29:00 GMT

Is there a reason you have them as a dimension and not expression?

Best,

Re: Subtracting AGGR equations

Thu, 12 Mar 2015 15:47:47 GMT

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

,FIELD)

Seems to be working - thanks all for your suggestions!

Mel

Re: Subtracting AGGR equations

sunny_talwar — Thu, 12 Mar 2015 15:58:02 GMT

I would suggest marking your answer as correct so that the thread can be closed.

Best,