topic Re: AGGR in QLikview Script in QlikView

AGGR in QLikview Script

dunnalahk123 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have one Parent id to that Actual value is 3 but same parent ID has 3 Subtasks so when i wrote Sum(Actual) i am getting 10 but problem is Sum (Parent Actual ) i should get only 3 as it has same project iD so my Total should be 10+3 =13.

but right now i am getting 10+9=19 which is wrong .

So how can i write it in back end Qlikview Script.

Re: AGGR in QLikview Script

sunny_talwar — Thu, 12 Mar 2020 19:35:14 GMT

What exactly are you looking to do in the script? You want to do the Sum() in the script?

Re: AGGR in QLikview Script

dunnalahk123 — Fri, 13 Mar 2020 08:03:28 GMT

Hi Sunny,

Yes , Basically in the example i have given 1 parent value is getting repeated for 3 times as it has 3 child.

My Numerator is Sum(Parent Actual) >>> right now i am getting 3+3+3=9 but i should get once per Parent. so i should get 3 as my numerator as Parent is same . (Basically it should sum only once per Parent)

Sum(Parent Actual) as Numerator----It should come only once per Parent ( this how can i write in back end script ?)
Sum(Actual) as Denominator -- no problem as i am summing at child level it comes correctly.

Re: AGGR in QLikview Script

tresB — Fri, 13 Mar 2020 08:41:41 GMT

May be like:

Sum(Distinct [Parent Actual])

Re: AGGR in QLikview Script

sunny_talwar — Fri, 13 Mar 2020 11:12:52 GMT

Or this

Sum(Aggr(Sum(DISTINCT [Parent Actual]), [Parent]))

Re: AGGR in QLikview Script

dunnalahk123 — Fri, 13 Mar 2020 12:55:09 GMT

Hi Sunny,

can i give it in back end script ? i tried in back end script but its not taking it .

Re: AGGR in QLikview Script

dunnalahk123 — Fri, 13 Mar 2020 12:56:39 GMT

Hi,

I tried in back end script but it failed saying invalid expression.

Re: AGGR in QLikview Script

sunny_talwar — Fri, 13 Mar 2020 13:01:33 GMT

You want to pre-aggregate the data in the script? Is that what you are trying to do?

Re: AGGR in QLikview Script

dunnalahk123 — Fri, 13 Mar 2020 13:55:27 GMT

Hi Sunny,

Yes, when pull to front end it should be just sum(numerator ) / sum(denominator) everything should be derived at back end script itself.

Re: AGGR in QLikview Script

dunnalahk123 — Fri, 13 Mar 2020 17:09:05 GMT

Hi Sunny,

Please let me know if i am not clear.

Best Regards,

Re: AGGR in QLikview Script

dunnalahk — Mon, 16 Mar 2020 09:48:30 GMT

It's Working Now. Thank You.

Re: AGGR in QLikview Script

sunny_talwar — Mon, 16 Mar 2020 10:55:22 GMT

To do it in the script... you can use Group by statement... for example

tmpAggr: LOAD Parent, Sum(DISTINCT [Parent Actual]) as SumParent, Sum(Actual) as SumChild, Sum(DISTINCT [Parent Actual]) + Sum(Actual) as SumOverall From/Resident ... Group By Parent;

Re: AGGR in QLikview Script

dunnalahk — Mon, 16 Mar 2020 11:05:05 GMT

Thank You Sunny , it's working now. i made a silly syntax error mistake .

Re: AGGR in QLikview Script

sunny_talwar — Mon, 16 Mar 2020 11:11:28 GMT

Awesome 🙂