topic Re: Sum each last value by field in QlikView

Sum each last value by field

Hakim-A — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I am facing a problem I can't solve.

I have data like this :

And I'd like to display for each chassis 1, 2 & 3 the sum of each last value of counter (by last value, it is the value of counter where day=max(day)) for all variants, so this result:

I could then filter on a certain variant and see the value for this specific variant

For example if I select A & C I'd have :

Thanks for your contribution, it really drives me crazy not to find a way to solve this.

Re: Sum each last value by field

Anil_Babu_Samineni — Sat, 15 Oct 2016 18:04:25 GMT

How you are calculating Sum Latest Counter

Re: Sum each last value by field

sunny_talwar — Sat, 15 Oct 2016 21:45:09 GMT

Try this expression:

=Sum(Aggr(FirstSortedValue(Counter, -Day), Chassis, Variant))

Re: Sum each last value by field

Hakim-A — Sun, 16 Oct 2016 09:01:36 GMT

Hi,

Thanks for the quick replies.

Sum latest counter is calculated as so = For each chassis we sum the values of counter of all distinct Variant where day=max(day)

Sunny, your solution is not working I tried it with my data and I've errors.

See enclosed if you select chassis b-749444 and variant u3000 then the result is 20 and it should be 39 787

In fact there are double values for this chassis and variant so I think QV is confused

See in the excel file you'll understand immediately

Re: Sum each last value by field

sunny_talwar — Sun, 16 Oct 2016 21:59:04 GMT

Do you mean 39867?

May be try this:

=Sum(Aggr(FirstSortedValue(Aggr(Sum(counter), chassis, variant), -Aggr(day, chassis, variant)), chassis, variant))

Re: Sum each last value by field

Hakim-A — Mon, 17 Oct 2016 06:58:49 GMT

Actually the result should be 39 792 : 1+13+1+5+6+39766

For each chassis having the same variant and same day we should take the maximum of counter

The formula is yet not valid really strange

Thank you for contribution

Re: Sum each last value by field

Anonymous — Mon, 17 Oct 2016 07:29:44 GMT

Hi hakim,

You can flag your data on script:

LEFT JOIN

LOAD

Max(day) as day,

chassis,

variant,

1 as LastDayFlag

RESIDENT Sheet3

GROUP BY chassis, variant;

Then, on your expression:

=Sum({<LastDayFlag={1}>}counter)

Regards!!

Re: Sum each last value by field

sunny_talwar — Mon, 17 Oct 2016 16:12:35 GMT

This is a new information which you did not tell before. Try this

=Sum(Aggr(FirstSortedValue(Aggr(Max(counter), chassis, variant), -Aggr(day, chassis, variant)), chassis, variant))

Re: Sum each last value by field

Hakim-A — Mon, 21 Nov 2016 12:33:15 GMT

Sorry for late reply

I managed to build a solution thanks to your contributions.

Many thanks