Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
Hi,
Try something like: Sum(If(Mod(Num(Month(DATE_FIELD)), 3) = 0, Sales))
Regards,
H
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.
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
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
Thank you, Oscar
This logic will work when we select any particular Quarter in the time period, I need sales irrespective of Quarter selection.
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
Not Working, I tried your logic in sample document shared by Munoz Hector .
Could you please check this.
Regards
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?
Something like this then?
Sum( {< YearM={$(=Max( {1< YearQ= >} YearM))}, YearQ= >} Sales )