topic Re: Sales for the largest month expression in QlikView

Sales for the largest month expression

aaronnayan — Wed, 25 Nov 2020 16:16:04 GMT

Hi Guys

I want to create a variable,

Is there a way for me to calculate the month with the largest sales

Mont field is "DEC_Month"

Sales field is "SALES"

N.B . It should only show one month

Re: Sales for the largest month expression

Anil_Babu_Samineni — Thu, 17 Aug 2017 09:40:25 GMT

This?

FirstSortedValue(DEC_Month, -Max(SALES))

Re: Sales for the largest month expression

aaronnayan — Thu, 17 Aug 2017 09:41:51 GMT

No sorry anil that doesnt work

Re: Sales for the largest month expression

Anil_Babu_Samineni — Thu, 17 Aug 2017 09:44:34 GMT

My Bad, Should work

FirstSortedValue(DEC_Month, -Aggr(Max(SALES), DEC_Month))

Re: Sales for the largest month expression

tresB — Thu, 17 Aug 2017 09:45:22 GMT

Try like:

FirstSortedValue(DEC_Month, -Aggr(Max(SALES),DEC_Month))

Re: Sales for the largest month expression

aaronnayan — Thu, 17 Aug 2017 09:46:04 GMT

Hi Anil

If i was to save your expression

I would create a variable

FirstSortedValue(DEC_Month, -Max(SALES))>>> vTopMonth

Then in my chart i would use this

Sum({1<DEC_Month={'$(vTopMonth)'}>}SALES)

If you see errors please tell me

Kind Regards

Re: Sales for the largest month expression

Anil_Babu_Samineni — Thu, 17 Aug 2017 09:57:53 GMT

Use this in variable

FirstSortedValue(DEC_Month, -Aggr(Max(SALES), DEC_Month))

Then, Set analysis should be this?

Sum({1<DEC_Month = {"$(=$(vTopMonth))"}>} SALES)

Re: Sales for the largest month expression

aaronnayan — Thu, 17 Aug 2017 10:01:45 GMT

Very Very close its giving a result,

but its not selecting the largest month 😮

Its selecting the middle month if that helps

I have 3 months and its selecting the middle one, not the lowest or the highest.

Could it be the varible?

Re: Sales for the largest month expression

tresB — Thu, 17 Aug 2017 10:02:55 GMT

You could even directly try like:

Sum({1<DEC_Month={"=DEC_Month=Max(total SALES)"}>}SALES)

Re: Sales for the largest month expression

Anil_Babu_Samineni — Thu, 17 Aug 2017 10:04:13 GMT

Check testing in text object and tell me whether you are getting any thing or not?

=vTopMonth

Re: Sales for the largest month expression

aaronnayan — Thu, 17 Aug 2017 10:06:28 GMT

Hi Trescco

Sorry that expression doesn't work

it could be this part vv

Sum({1<DEC_Month={"=DEC_Month=Max(total SALES)"}>}SALES)

Re: Sales for the largest month expression

aaronnayan — Thu, 17 Aug 2017 10:07:57 GMT

Hi Anil

Its giving me the expression, not the result in the text box

Re: Sales for the largest month expression

Anil_Babu_Samineni — Thu, 17 Aug 2017 10:09:26 GMT

My bad, This?

=$(vTopMonth)

Re: Sales for the largest month expression

aaronnayan — Thu, 17 Aug 2017 10:11:34 GMT

HAHAha

its giving the result JULY

I have 3 months data

The highest month sales is June

The lowest month sales is August

Re: Sales for the largest month expression

Anil_Babu_Samineni — Thu, 17 Aug 2017 10:14:27 GMT

Strange, How come JULY will come. May be share application to test?

Note - Please mark as helpful if you found some useful information from community of thread. Then only we can understand to link start from there

Re: Sales for the largest month expression

aaronnayan — Thu, 17 Aug 2017 10:17:21 GMT

Im sorry Anil im unable to share the application

it is very strange

Is there no way around this?

Re: Sales for the largest month expression

Anil_Babu_Samineni — Thu, 17 Aug 2017 10:19:47 GMT

This is really intriguing to troubleshoot. Off course, there must be some other round. But, Try this in list box and show the image of DEC_Month