topic Re: Calculate rank based on current month vs previous month in QlikView

Calculate rank based on current month vs previous month

Wed, 03 Feb 2016 15:29:31 GMT

Greetings

Please help to obtain the desired result for calculating ranks.

I would like to rank the products based on the difference of current month and prior month.

(sum({<product= {"=rank(sum(qty), product<=$(vRank)"}, year = 2016 , month = 2 >} qty) )

(sum({<product= {"=rank(sum(qty), product<=$(vRank)"}, year = 2016 , month = 1 >} qty) )

Thanks!

Re: Calculate rank based on current month vs previous month

swuehl — Wed, 03 Feb 2016 15:31:11 GMT

Sorry, I don't understand.

This is not correct syntax:

{"=rank(sum(qty), product<=$(vRank)"}

Maybe you want

{"=rank(sum(qty))<=$(vRank)"}

Re: Calculate rank based on current month vs previous month

Wed, 03 Feb 2016 15:32:48 GMT

Thanks for responding.

The rank must based on the difference of qty of Month 2 and 1.

Re: Calculate rank based on current month vs previous month

swuehl — Wed, 03 Feb 2016 15:35:36 GMT

Maybe create a straight table with dimension product and as expression

=Rank(

(sum({< year = 2016 , month = 2 >} qty) )

(sum({< year = 2016 , month = 1 >} qty) )

)

Re: Calculate rank based on current month vs previous month

Wed, 03 Feb 2016 15:55:03 GMT

It's working if the default selection is for all.

If I calculate for top N then not working.

Re: Calculate rank based on current month vs previous month

swuehl — Wed, 03 Feb 2016 15:57:10 GMT

Not sure what you are telling me or what you are trying to do.

Could you upload a small sample QVW and describe based on your sample the requested result?

Re: Calculate rank based on current month vs previous month

Wed, 03 Feb 2016 20:25:20 GMT

I would like to achieve the TOP 5 products based on the differences.

Please find attach the sample qvw file.

Re: Calculate rank based on current month vs previous month

swuehl — Wed, 03 Feb 2016 20:33:30 GMT

Maybe like this

Re: Calculate rank based on current month vs previous month

Wed, 03 Feb 2016 20:55:59 GMT

If I will select top 6 then the result is showing incorrect.

Please find below:

Re: Calculate rank based on current month vs previous month

swuehl — Wed, 03 Feb 2016 21:01:10 GMT

Your are still using the old buggy expression, use

(sum({< year = {$(vMaxYear)}, month= {$(vMaxMth)}>} qty) ) -
(sum({< year = {$(vMaxYear)}, month= {$(vLstMth)}>} qty) )

instead.

Re: Calculate rank based on current month vs previous month

Wed, 03 Feb 2016 21:22:46 GMT

Many thanks! It's Working.