Calculating Max Month Sales in table

[polisetti]

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!

[polisetti]

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.

[oscar_ortiz]

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

[polisetti]

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

[oscar_ortiz]

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

[oscar_ortiz · Accepted solution]

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.

[oscar_ortiz]

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

[sugathirajkumar]

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