# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for
Did you mean:
Creator

## Calculating Max Month Sales in table

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!

1 Solution

Accepted Solutions
Partner

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.

16 Replies
Specialist

Hi,

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

Regards,
H

Creator
Author

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.

Partner

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

Specialist

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,

H

Creator
Author

Thank you, Oscar

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

Partner

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

Creator
Author

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

Regards

Partner

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?

Partner

Something like this then?

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

Community Browser