topic Rank and Sum function with variables in QlikView

Rank and Sum function with variables

Tue, 28 Feb 2012 16:15:07 GMT

I am trying to create a table that dynamically updates to show the top 4 product Revenues based on a Salesperson selection.

I am using a variable so that the timeframe can be easily updated (called vmaxmonth)

I am having trouble with my rank and sum equations using the variable vmaxmonth

The issue seems to be related to the variable being contained within the rank function:

works:

=sum({$<Year={2012},Month = {"<=$(=vmaxmonth)"},

ProdFinal={"=rank(sum({$<Year={2012},Month={1,2}>}Net)

,4,1)=1"}>} Net)/vfactor

does not work :

=sum({$<Year={2012},Month = {"<=$(=vmaxmonth)"},

ProdFinal={"=rank(sum({$<Year={2012},Month={"<=$(<=vmaxmonth)"}>}Net)

,4,1)=1"}>} Net)/vfactor

thanks in advance

swuehl — Tue, 28 Feb 2012 16:18:56 GMT

Try this:

=sum({$<Year={2012},Month = {"<=$(vmaxmonth)"},

ProdFinal={"=rank(sum({$<Year={2012},Month={"<=$(vmaxmonth)"}>}Net)

,4,1)=1"}>} Net) / vfactor

Tue, 28 Feb 2012 16:26:12 GMT

unfortunately this does not work. it doesnt seem to work with the "<=$(vmaxmonth)" within the rank function

swuehl — Tue, 28 Feb 2012 16:31:26 GMT

Ah, I now see that you are embedding double quotes in double quotes, I think that's the problem. Let me think...

swuehl — Tue, 28 Feb 2012 17:11:38 GMT

Try creating a new variable vmaxmonthexp in variable overview:

=concat({<Month={"<=$(vmaxmonth)"}>} distinct Month,',')

then you could use this variable in the set element(s) instead of "<=$(vmaxmonth)":

=sum({$<Year={2012},Month = {$(vmaxmonthexp)},

ProdFinal={"=rank(sum({$<Year={2012},Month={$(vmaxmonthexp)}>} Net)

,4,1)=1"}>} Net) / vfactor

Maybe I have missed something easier, but this should work.

Regards,

Stefan

Tue, 28 Feb 2012 17:26:06 GMT

works, thank you!

suvechha_b — Thu, 24 Oct 2013 07:55:40 GMT

Can you please help me out ...

please check the below link..