topic Re: Distinct summation in QlikView

Distinct summation

Tue, 25 Mar 2014 18:24:46 GMT

Hi All,

I have a database with a number of fields, of which the key ones for this question are Customer number, Date & yield. A customer can have a number of entries in this database. I am trying to produce a bar chart for the customer yield by area. The problem I have is that when I do a summation of the yield, I am duplicating, triplicating etc the value of the yield field. I would like to do a summation of the yield field, but based on the last entered value for yield, so that for each distinct customer number, I am only picking up one value of "yield".

Can someone assist with how the expression might look like?

Thank you

Herbert

Re: Distinct summation

MK_QSL — Tue, 25 Mar 2014 18:38:02 GMT

Can you provide sample excel file or sample apps with little data?

Re: Re: Distinct summation

Tue, 25 Mar 2014 20:26:40 GMT

Hi Manish,

Thanks for the reply. Please find attached a small example I have put together. On the attached data, how would I sum up by area all the latest yield estimates bearing in mind that the customer ID's I have highlighted in green have more than one value, and I only want to add the latest value from each customer.

Many Thanks

Herbert

Re: Re: Re: Distinct summation

Tue, 25 Mar 2014 20:43:23 GMT

Maybe like attached.

Thanks

Re: Distinct summation

maxgro — Tue, 25 Mar 2014 21:21:57 GMT

sum(aggr(FirstSortedValue([Latest Yield Estimate], -Date), [Customer ID], Area))

Re: Distinct summation

MK_QSL — Wed, 26 Mar 2014 08:03:52 GMT

Something like below?

=============================================================

Temp:

Load * Inline

[

Customer ID , Date, Area, Latest Yield Estimate

96989, 01/03/2014, CA, 15000

85969, 01/03/2014, NYC, 20151

45551, 01/03/2014, NYC, 9000

15869, 01/03/2014, LA, 7000

765232, 01/03/2014, NYC, 5000

86545, 02/03/2014, NC, 6000

86748, 03/03/2014, NC, 4000

85969, 04/03/2014, NYC, 8900

15869, 04/03/2014, LA, 2540

878435, 04/03/2014, IN, 9860

782121, 04/03/2014, IN, 7400

96989, 04/03/2014, CA, 6500

];

Join

Load

[Customer ID],

FirstSortedValue([Latest Yield Estimate],-Date) as RequiredSumResult

Resident Temp

Group By [Customer ID];

===================================

Now Create a Bar Chart

Dimension = Area

Expression = SUM(RequiredSumResult)

Hope this helps...

Re: Distinct summation

Wed, 26 Mar 2014 08:32:25 GMT

Thanks Massimo,

This works perfectly. I like the solution, because it works with my current data without having to go back and redo load statements etc.

Much appreciated.

Herbiec