topic Re: Calculating Max Month Sales in table in QlikView

Calculating Max Month Sales in table

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

Hi Everyone,

We are calculating sales metric at Month and Quarter time periods. However, the requirement is to show only latest Month sales in a quarter at the Quarter time period. So when dimension is selected as Quarter, we have to show Sales metric by individual quarter and sales need to show for latest month in that quarter (don't want to sum up all 3-month sales is a quarter)

How can I achieve this by making changes directly in chart expression?

Thanks!

Re: Calculating Max Month Sales in table

hector_munoz — Wed, 28 Jun 2017 12:21:04 GMT

Hi,

Try something like: Sum(If(Mod(Num(Month(DATE_FIELD)), 3) = 0, Sales))

Regards,
H

Re: Calculating Max Month Sales in table

polisetti — Wed, 28 Jun 2017 12:35:34 GMT

Thank you, this logic is working. However, instead of If the condition can we write in Set Analysis?

Also, I need Maximum available Month in that Quarter not always picking the 3 month in the quarter. Ex: If we have April data as Max, then in Q2 it should pick the latest Month in the quarter and it will be April.

Re: Calculating Max Month Sales in table

oscar_ortiz — Wed, 28 Jun 2017 12:55:11 GMT

Try something like this:

I prefer setting up my calendar with a YearMonth field for something like this.

Sum( {< YearMonth={$(=Max(YearMonth))} >} Sales )

Sum( {< Month={$(=Max(Month))} >} Sales )

Good luck

Oscar

Re: Calculating Max Month Sales in table

hector_munoz — Wed, 28 Jun 2017 13:01:21 GMT

Hi Jaswanth,

Try the following expression:

Sum(If(Mod(Num(Month(Date#(YearM, 'YYYYMM'))), 3) = 0 OR Text(Date#(YearM, 'YYYYMM')) = Text($(=MaxString(TOTAL YearM))), Sales))

I attach you a sample. I donot know how to do it with set analysis.

Regards,

Re: Calculating Max Month Sales in table

polisetti — Wed, 28 Jun 2017 13:38:38 GMT

Thank you, Oscar

This logic will work when we select any particular Quarter in the time period, I need sales irrespective of Quarter selection.

Re: Calculating Max Month Sales in table

oscar_ortiz — Wed, 28 Jun 2017 13:44:30 GMT

You may need to add another selection criteria into your set analysis.

Sum( {< YearMonth={$(=Max(YearMonth))}, Quarter= >} Sales )

This will ignore any selections in your Quarter field.

Thanks

Oscar

Re: Calculating Max Month Sales in table

polisetti — Wed, 28 Jun 2017 14:28:55 GMT

Not Working, I tried your logic in sample document shared by Munoz Hector .

Could you please check this.

Regards

Re: Calculating Max Month Sales in table

oscar_ortiz — Wed, 28 Jun 2017 14:40:40 GMT

So is the result you're looking to be returned that if you select 20163 as the quarter you still want to show the result for month 201702?

Re: Calculating Max Month Sales in table

oscar_ortiz — Wed, 28 Jun 2017 14:43:58 GMT

Something like this then?

Sum( {< YearM={$(=Max( {1< YearQ= >} YearM))}, YearQ= >} Sales )

Re: Calculating Max Month Sales in table

polisetti — Wed, 28 Jun 2017 14:47:08 GMT

No, it needs to show only the Max Month Sales of that Quarter. In the Stright table, the dimension is Quarter and when I am applying your logic, it is only showing values for the latest quarter. and for other quarter null is displaying.

See the attached file.

Re: Calculating Max Month Sales in table

oscar_ortiz — Wed, 28 Jun 2017 14:54:39 GMT

There is only a single month for Q1 2017 with a value 1300. So it looks to me like this expression is actually working.

Re: Calculating Max Month Sales in table

polisetti — Wed, 28 Jun 2017 15:01:36 GMT

But I need output for other Quarter as well. As coming in expression 2.

Re: Calculating Max Month Sales in table

oscar_ortiz — Wed, 28 Jun 2017 15:21:33 GMT

I would then build that into the script load. I'll try to come up with an example shortly.

Re: Calculating Max Month Sales in table

oscar_ortiz — Wed, 28 Jun 2017 15:40:59 GMT

Have a look at the attached QVW. I believe this is what you are looking for. One thing to be aware of is that the table that is being loaded is already sorted by date. If it isn't you'll want to make sure you sort your table.

Good luck

Oscar

Just another thought, a well built master calendar could easily be used in this situation adding to the calendar an expression such as what we did in the script. I find that dealing with dates in set analysis is much easier done when you are working with a master calendar.

Re: Calculating Max Month Sales in table

oscar_ortiz — Thu, 29 Jun 2017 13:59:37 GMT

Jaswanth,

If you've found the advice helpful or correct please close the post.

Qlik Community Tip: Marking Replies as Correct or Helpful

Thanks

Oscar

Re: Calculating Max Month Sales in table

sugathirajkumar — Fri, 23 Aug 2019 10:12:23 GMT

=FirstSortedValue( aggr(sum(Amount),Monthnum), -Monthnum) try to keep the month in numbers