topic Re: Median of Latest Date for each product in QlikView

Median of Latest Date for each product

montubhardwaj — Mon, 11 Mar 2019 04:15:14 GMT

Hello,
I need your suggestions with this problem. Please refer to the attached qvw file. What is thew best way to find average and median for Latest Date Values (0.2,0.56,0.96) and Previous Date Values (0.25,0.59,0.66) for all the products. I was able to figure out Factor value for all products for latest and previous date but having trouble with average and median. I want the result to be displayed in a text box object.
Thanks for your help.

Re: Median of Latest Date for each product

montubhardwaj — Mon, 11 Mar 2019 04:13:47 GMT

@rubenmarin maybe you have the answer already!

Re: Median of Latest Date for each product

jonathandienst — Mon, 11 Mar 2019 04:59:43 GMT

If you add a vintage* value by product then this becomes quite easy. The load script needs a small modification:

test1:
LOAD * ,
RowNo() As Row#
INLINE [
    Product, Factor, Date
    Product_A, 0.20, 1/3/2018
    Product_A, 0.25, 1/3/2018
    Product_A, 0.30, 1/2/2018
    Product_A, 0.35, 1/1/2018
    Product_B, 0.56, 12/3/2018
    Product_B, 0.59, 12/3/2018
    Product_B, 0.55, 12/2/2018
    Product_B, 0.86, 12/1/2018
    Product_C, 0.96, 2/3/2018
    Product_C, 0.66, 2/2/2018
    Product_C, 0.68, 2/2/2018
    Product_C, 0.99, 2/1/2018
];

Test2:
LOAD *,
	AutoNumber(Date + Row# / 1e6, Product) as Vintage
Resident test1
Order By Product, Date DESC;

DROP TABLE test1;

The auto number creates unique values for each product for the last date (=1) and the next last date (=2). So the average and median expression for the last date and the precious date would be:

=Avg({<Vintage={1}>} Factor)
=Median({<Vintage={1}>} Factor)

=Avg({<Vintage={2}>} Factor) 
=Median({<Vintage={2}>} Factor)

You could also simplify your sums:

=Sum({<Product={'Product_C'}, Vintage={1}>} Factor)

Note the row number is used to de-duplicate the dates, so if there are two value for the most recent date, the first loaded date will be used. If the intention was to get the sum / average and median for all the Factors for a product for the last date and previous date, then just use:

...
AutoNumber(Date, Product) as Vintage
...

in the script. The text box expressions would not need to be adjusted.

* a sequence value that starts from the last date, incrementing the sequence by date working backwards (in this case).

Re: Median of Latest Date for each product

montubhardwaj — Mon, 11 Mar 2019 05:25:10 GMT

Wow, this is masterclass. Thanks much. Makes sense with the script. What if we only need to achieve it using expressions? I am just curious to see your solution.

Re: Median of Latest Date for each product

montubhardwaj — Mon, 11 Mar 2019 05:33:18 GMT

Also if you can explain little about the concept you used in Autonumber, that would be very helpful @jonathandienst

Re: Median of Latest Date for each product

jonathandienst — Mon, 11 Mar 2019 14:24:21 GMT

I would not do this in expressions myself. while it may be possible, it will probably be quite complex.

AutoNumber() used with two parameters will number the first parameter in a group made up of the second parameter. That sounds clunky, so let me try again. Each distinct value of parameter 2 makes up a new list starting at 1 and looks up and adds the next number in that list.

So here, there will be an autonumber sequence for each Product. Value = 1 would be the first date found for that order. Value = 2 will be the next, etc. The Order By will determine if you start with the smallest date or the largest date.

Re: Median of Latest Date for each product

montubhardwaj — Tue, 12 Mar 2019 03:40:12 GMT

Thank you explaining it so nicely. Have a great day/night.