topic Re: Set Analysis sum max date qty in App Development

Set Analysis sum max date qty

rubenares — Fri, 22 Nov 2019 10:10:04 GMT

Working with the following dataset:

Customer	Date	Qty
A	31/3/2019	0.9
A	31/3/2019	0.2
A	30/6/2019	0.2
A	30/6/2019	0.2
B	30/6/2018	0.6
B	30/9/2018	0.4
B	30/9/2018	0.2

We want to get following result:

Customer	Last Date	Last Qty
A	30/6/2019	0.4
B	30/9/2018	0.6

We have resolved Last Date measure with following code:

Max({<Date =>} Date )

But we have problems to create Last Qty measure, we are trying with following code:

Sum({<Date = {"$(=Max({<Date =>} Date ))"}>} [Qty])

But it calculates max date globally, not by customer.

Aditionally, these measures should ignore date selection.

Thanks in advance!

Re: Set Analysis sum max date qty

tresB — Fri, 22 Nov 2019 10:24:49 GMT

Try:

FirstSortedValue( {<Date>} Aggr({<Date>} Sum({<Date>} Qty), Customer, Date), -Aggr(Date,Date))

Re: Set Analysis sum max date qty

sunny_talwar — Fri, 22 Nov 2019 15:38:39 GMT

If I may, I think we should ignore selection of Date within Aggr(Date, Date) also. and I am not sure if this is important or not, but I usually use the same Aggr() dimensions as the first part.

FirstSortedValue({<Date>} Aggr(Sum({<Date>} Qty), Customer, Date), -Aggr(Only({<Date>}Date), Customer, Date))