topic Re: top n values in current month comparing to previous month in QlikView

top n values in current month comparing to previous month

Tue, 01 Nov 2016 20:16:47 GMT

Hi All

please help me out in

Comparing the current month top n values to the previous months

Re: top n values in current month comparing to previous month

johnw — Tue, 01 Nov 2016 20:39:10 GMT

Just a typo, perhaps. You're doing sum(country) instead of sum(Orders).

Re: top n values in current month comparing to previous month

ecolomer — Tue, 01 Nov 2016 20:50:47 GMT

As John, say you need change Country by Orders

Re: top n values in current month comparing to previous month

Wed, 02 Nov 2016 02:52:26 GMT

Hi john i tried the other way as well but no luck

Can u pls help me

Re: top n values in current month comparing to previous month

Wed, 02 Nov 2016 02:52:58 GMT

I didnt get that can you pls explain

Re: top n values in current month comparing to previous month

ecolomer — Wed, 02 Nov 2016 04:08:27 GMT

In your expression you have "country" and is "orders"

Re: top n values in current month comparing to previous month

Wed, 02 Nov 2016 09:21:24 GMT

Its not geeting yhe correct results

Re: top n values in current month comparing to previous month

tresB — Wed, 02 Nov 2016 09:39:51 GMT

Perhaps like this:

sum({<country={"=rank(Sum({<OrderDate={'>=$(MonthStart(Max(OrderDate))))<=$(=Date(Max(OrderDate)))'}>}Orders))<4"},

OrderDate={'>=$(MonthStart(Max(OrderDate),-2)))<=$(=Date(Max(OrderDate)))'}>}Orders)

Re: top n values in current month comparing to previous month

Aurelien_Martinez — Wed, 02 Nov 2016 10:39:45 GMT

Hi,

Try this expresion : sum({<country={"=rank(aggr(Sum(Orders), country), 4, 1)<=3"}>} Orders)

Aurélien

Re: top n values in current month comparing to previous month

johnw — Wed, 02 Nov 2016 18:05:30 GMT

When I fix the expression as I suggested, I get the results you requested. The only difference I see is they aren't sorted in the order you show. If that's important to you, sort your second dimension like this:

rank(sum({<MonthYear={"$(=max(MonthYear))"}>} Orders),4,1)

I think you can also just use this as your expression:

sum(Orders)

Re: top n values in current month comparing to previous month

Thu, 03 Nov 2016 16:06:17 GMT

Hi John

When i select the march month, the data is displaying wrong and for the remining months as well.

In march month top 3 are

India, japan and korea

but these values are displaying wrong in the comparision chart

when these values are compared to the previous month, the values are also wrong

can you please help me

i have the as of calender, where i can show the current month data with currentmonth =1 flag

i can able to show the previous 5 months as well with isprevious5 =1 flag ( which you sorted out) in the bel;ow thread

Current month average comparision with previous months average

but when there are only 2 top values in the current month the problem is araising

top 2 values in the current month are displaying as well as other values from other months are also displaying

Re: top n values in current month comparing to previous month

Thu, 03 Nov 2016 16:20:46 GMT

Hi Aurelien,

When i select the march month, the data is displaying wrong and for the remining months as well.

In march month top 3 are

India, japan and korea

but these values are displaying wrong in the comparision chart

when these values are compared to the previous month, the values are also wrong

the top 3 in march should display in previous months as well if there is no data for those countries in previous month show it as null or no bar

top 3 values in the current month are displaying wrong as well as other values from other months are also displaying

Re: top n values in current month comparing to previous month

Thu, 03 Nov 2016 16:36:17 GMT

Hi Tesesco

When i select the march month, the data is displaying wrong and for the remining months as well.

In march month top 3 are

India, japan and korea

but these values are displaying wrong in the comparision chart

when these values are compared to the previous month, the values are also wrong

the top 3 in march should display in previous months as well if there is no data for those countries in previous month show it as null or no bar

top 3 values in the current month are displaying wrong as well as other values from other months are also displaying

Re: top n values in current month comparing to previous month

johnw — Thu, 03 Nov 2016 20:00:15 GMT

Is this what you want for March? I guess you said the 300 is wrong, but I'm unclear why it's wrong. It seems right to me.

To get this chart, I changed the second dimension to this:

=aggr(if(rank(sum({<MonthYear={"$(=max(MonthYear))"}>} Orders),4,1)<=3,country),country)

Re: top n values in current month comparing to previous month

Fri, 04 Nov 2016 17:17:52 GMT

Hi John,

The requiremnt is, what ever the month user selects

The top3 in that month should be shown

and want to see how these top 3 in selcted month are in previous 3 months

for march and feb, it is showing correct results

where as when i select april its again not showing correct results

there is only one value in april ie usa

but in the previous months there is no data for usa so there should be no data shown

but the bar is showing other countries data as well

Re: top n values in current month comparing to previous month

Fri, 04 Nov 2016 18:20:22 GMT

Hi John,

The above expression is working fine for me in all cases expect the below case

if there is only one value in current month, the previous months should also show one value (ie the value which is in current month) else it should not show but with the above expression, the previous months top values are also displaying along with the current values.

in the below chart

i have only 1 order in may and that order is showing in the previous 3 months but along with that the other values are also showing which shouldn't

Re: top n values in current month comparing to previous month

johnw — Fri, 04 Nov 2016 19:35:29 GMT

OK, I recoded/simplified your Calendar and AsOf script from scratch so that everything was defined and connected more like I'm used to. The chart seems to be working correctly now for all months, if I understand how it's supposed to work. Sorry for all this confusion. You asked for something that seems like it should be so easy, and it's turned out to be so difficult to get right. I sure hope we have it this time.

Dimension 1 = Month
Dimension 2 = Country
Expression = sum({<Country={"=rank(sum({<IsCurrent={1}>} Orders),4,1)<4"}>} Orders)

Re: top n values in current month comparing to previous month

Sat, 05 Nov 2016 09:16:44 GMT

Hi John

You are absolute genious, great and the qlik guru, thanks a ton, that solved the problem.

One last thing

= sum({<Country={"=rank(sum({<IsCurrent={1}>} Orders),4,1)<4"}>} Orders)

this gives me the exact results

but i want to always see the last 3 months data when ever i selecta month

ie the isrolling3 flag should be always 1, where can i insert the isrolling3 flag into this setexpression

Re: top n values in current month comparing to previous month

johnw — Mon, 07 Nov 2016 17:19:53 GMT

Like this:

sum({<IsRolling3={1},Country={"=rank(sum({<IsCurrent={1}>} Orders),4,1)<4"}>} Orders)

Re: top n values in current month comparing to previous month

Mon, 07 Nov 2016 18:57:52 GMT

Thank you.... Thank you.... Thank you.... Thank you ..... its working perfect

Can you please xplain me the logic please