Sum each last value by field

[Hakim-A]

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.

[Anil_Babu_Samineni]

How you are calculating Sum Latest Counter

[sunny_talwar]

Try this expression:

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

[Hakim-A]

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

[sunny_talwar]

Do you mean 39867?

May be try this:

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

[Hakim-A]

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

[Anonymous · Accepted solution]

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!!

[sunny_talwar]

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))

[Hakim-A]

Sorry for late reply

I managed to build a solution thanks to your contributions.

Many thanks