Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
polisetti
Creator II
Creator II

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
oscar_ortiz
Partner - Specialist
Partner - Specialist

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.

View solution in original post

16 Replies
hector_munoz
Specialist
Specialist

Hi,

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

Regards,
H

polisetti
Creator II
Creator II
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.

oscar_ortiz
Partner - Specialist
Partner - Specialist

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

hector_munoz
Specialist
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

polisetti
Creator II
Creator II
Author

Thank you, Oscar

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

oscar_ortiz
Partner - Specialist
Partner - Specialist

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

polisetti
Creator II
Creator II
Author

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

Could you please check this.


Regards

oscar_ortiz
Partner - Specialist
Partner - Specialist

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?

oscar_ortiz
Partner - Specialist
Partner - Specialist

Something like this then?

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