topic Re: average for last date in QlikView

average for last date

Madina — Fri, 22 Oct 2021 11:37:17 GMT

Hello everyone,

I have a data which have date, product_name , sale. I want to show the average for last date for particular product in gauge . The thing is that I have several records for each product for each date.

To make it clearer:

for example, product_name "a" has values for sale 50,10,20,30,40 for january 2021, has values for sale 500,100,200,300,400 for february 2021.

SoI want that gauge shows (500+100+200+300+400)/5 , because last date is february 2021.

Wanted to use FirstSortedValue( {$<product_name={'a'}>} sale, -date), but it takes null, because in this case for date we get several values

Re: average for last date

abhijitnalekar — Mon, 25 Oct 2021 09:06:20 GMT

Hi @Madina ,

Are you looking for the average for the max date month for the particular product?

and Is the divider(5) will be the constant for all the products or it will vary by product?

Re: average for last date

Madina — Mon, 25 Oct 2021 09:52:43 GMT

hi, @abhijitnalekar

no it may vary for each product, and even for any month. for example, this month you have 5 sales for product a, while on other month only 2.

And about date, yes, for max date, for example here max date is February