topic Re: Max or Min Date issue with Aggr function in QlikView

Max or Min Date issue with Aggr function

sheker_amrutham — Tue, 18 Apr 2017 12:28:33 GMT

Hi,

I have one table with below fields

Inventorytable:

LOAD * Inline [

InvDate, InvQuantity, Price, TotalSales

02-01-2014, 44, 4, 266

03-01-2014, 45, 4, 266

04-01-2015, 46, 4, 266

05-01-2015, 47, 4, 266

06-01-2016, 48, 4, 266

07-01-2016, 11, 4, 266

08-06-2017, 12, 4, 266

09-06-2017, 13, 4, 266

];

I am trying to display Sum(InvQuantity*Price) for Max(InvDate) of each and every Year, but my expression returing worng results.

Aggr(Sum({$<InvDate = {"$(=Date(Max(InvDate)))"}>}InvQuantity*Price),Year)

Please help me in solving this issue.

Please find the attached qvw file for more clarity.

Re: Max or Min Date issue with Aggr function

Anonymous — Tue, 18 Apr 2017 12:34:28 GMT

$(=Date(Max(InvDate))) is dimensionless if you use it like that

Re: Max or Min Date issue with Aggr function

tresB — Tue, 18 Apr 2017 12:50:25 GMT

Try like:

Max Date Sales: FirstSortedValue(Aggr(Sum(InvQuantity*Price),InvDate, Year), -InvDate)

Min Date Sales: FirstSortedValue(Aggr(Sum(InvQuantity*Price),InvDate, Year), InvDate)

Re: Max or Min Date issue with Aggr function

sheker_amrutham — Fri, 21 Apr 2017 06:46:47 GMT

Hi tresesco,

Thank you,

I am using below expression with one change in QlikSense

FirstSortedValue(Aggr(Sum(InvPriceBase*InvQuantity),Date, $(=_RebateTrendDIMField)), -Date)

_RebateTrendDIMField has Year, Quarter, Month loaded in Inline table.

The above expression did not show any value when I did not select any Year or Quarter or Month. If I select Year or Quarter or Month, Its working as Expected.

can you please suggest me.

Thanks,

Chandu

Re: Max or Min Date issue with Aggr function

Anonymous — Fri, 21 Apr 2017 07:32:15 GMT

why do you use

$(=_RebateTrendDIMField)) ? as dimension for aggr()?

however, you could try:

FirstSortedValue(distinct Aggr(Sum(InvPriceBase*InvQuantity),Date, $(=_RebateTrendDIMField)), -Date)

if this works, then there's more than 1 firstsortedvalue... but be careful, firstsortedvalue in combination with distinct returns only the first hit...