topic Re: AGGR with SET expression in QlikView

AGGR with SET expression

kakani87 — Sat, 30 Dec 2017 07:37:31 GMT

Dear All,

I want your help to find out something based on the aggr().

I'm having a data with me invoice,actual sale and sale but i want to display the actual sale using aggr() in the expression

expected value is 83000 as actual sale but it is showing as 149000

can any one help me .

PFA.

kakani87 — Sat, 30 Dec 2017 07:38:25 GMT

my expression as follows =sum(aggr(sum({<flag={'Asc'}>}actual_sales_val),sales_pk))

kakani87 — Sat, 30 Dec 2017 07:41:55 GMT

This exp is giving proper out put as expected

=sum(aggr(actual_sales_val,sales_pk))
but if i apply set expression it is not showing the same

=sum(aggr(sum({<flag={'Asc'}>}actual_sales_val),sales_pk))

pradosh_thakur — Sat, 30 Dec 2017 08:24:30 GMT

may be this

sum( {<flag={'Asc'} aggr(sum({<flag={'Asc'}>}actual_sales_val),sales_pk))

sum( total aggr(sum({<flag={'Asc'}>}actual_sales_val),sales_pk))

Why/where do you need aggr() though ? The output is 149000 for actual_sales_val and 83000 for sales. so i cant see what's wring there?

kakani87 — Sat, 30 Dec 2017 09:12:47 GMT

Hi Pradosh ....

lets say if actual sale is 6000 with in that sale value is considered as 3000 and 3000

let me explain this ...

for an invoice amount of 6000 is per an item where this was sold by 2 representatives 3000 and 3000

but invoice wise this is repeated for actualsale of 6000 based on employee

can you have a look at that sample once repeated invoice alias sales_pk can identify them

kakani87 — Sat, 30 Dec 2017 09:17:03 GMT

Above expression is throwing syntax error and below one returns the same 149000 value

pradosh_thakur — Sat, 30 Dec 2017 09:23:59 GMT

can you try this

sum( aggr(sum({<flag={'Asc'}>}actual_sales_val)/count({<flag={'Asc'}>}sales_pk),sales_pk))

kakani87 — Sat, 30 Dec 2017 09:56:00 GMT

Thank you Pradosh for supporting me inspite of weekend holiday .

Happy New Year In Advance .

pradosh_thakur — Sat, 30 Dec 2017 12:29:15 GMT

No problem man .. Wish you the same .. cheers.