Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
montubhardwaj
Specialist
Specialist

Median of Latest Date for each product

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.

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

6 Replies
montubhardwaj
Specialist
Specialist
Author

@rubenmarin  maybe you have the answer already!

jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
montubhardwaj
Specialist
Specialist
Author

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.
montubhardwaj
Specialist
Specialist
Author

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

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
montubhardwaj
Specialist
Specialist
Author

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