topic Re: Re: Help on Aggr in QlikView

Help on Aggr

Fri, 14 Nov 2014 06:08:18 GMT

Hi,

Attached is the file with two table Fact and Component. A different part number in the Fact could have the same component, each component has its own High or Low value.

I will need to present the details as well as summary table, the summation of the Amount in the Summary table will have to match with that of total amount in the details table.

I guess Aggr function could help.

Re: Help on Aggr

sujeetsingh — Fri, 14 Nov 2014 06:31:53 GMT

See the sample

Re: Help on Aggr

tresB — Fri, 14 Nov 2014 06:39:04 GMT

In the detail table expression include TOTAL like:

=Min(total High)/100 * Sum(AmountPounds)

Re: Help on Aggr

Fri, 14 Nov 2014 06:44:20 GMT

Hi Sujeet,

I would like the changes to be made on summary table not the details table,

Regards,

Rufus

Re: Help on Aggr

Fri, 14 Nov 2014 06:46:59 GMT

Details table what I have provided is correct, Details table holds all the line items which will take account of one High value hence Min(High) will return to the value in the line item. We do not have High field in Summary hence Min(High) will return to 0.1 and then further calculation takes place. Changes will have to be made in Summary table to match the sum in the Details table.

Re: Help on Aggr

tresB — Fri, 14 Nov 2014 06:50:15 GMT

This?

=Sum(High/100 * AmountPounds)

Re: Re: Help on Aggr

anbu1984 — Fri, 14 Nov 2014 06:50:25 GMT

Check this

=Sum(Aggr(Min(High)/100 * Sum(AmountPounds),Date,MaterialID,PartNumber,ProcessName,Component))

Re: Help on Aggr

sujeetsingh — Fri, 14 Nov 2014 06:55:14 GMT

Tresco has given a good logic it will work for you

Re: Help on Aggr

Fri, 14 Nov 2014 07:01:49 GMT

sum(aggr(Min(High)/100 * Sum(AmountPounds),MaterialID)) write above it will work

Re: Help on Aggr

Fri, 14 Nov 2014 07:36:08 GMT

Solution provided by tresesco & Santosh Works on the sample data but does not work on actual data. let me add some more data and upload it. Thanks for helping on this.

Re: Re: Help on Aggr

anbu1984 — Fri, 14 Nov 2014 07:38:04 GMT

Check my solution above works for you

Re: Help on Aggr

Fri, 14 Nov 2014 07:49:37 GMT

Thank you Santosh, your solution worked on the data set..

Re: Help on Aggr

Fri, 14 Nov 2014 07:50:37 GMT

Thank you Santosh, your solution worked on the data set..

Re: Help on Aggr

Fri, 14 Nov 2014 07:54:18 GMT

same as Santosh solution.. worked for me. Thank you!@