Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Reader,
I want to compare YTD sales of this year with YTD sales of last year.
Suppose I have a straight table with products A and B in the product dimension.
The selected period is 2014.
Expression 1 is sum(Sales)
Expression 2 is sum({$<Date={$(='">='&yearstart(max(Date),-1)&'<='&addyears(max(Date),-1)&'"')},Year>} Sales)
The table displays the data well. However, when i select 1 value in the product dimension of this chart, the set analysis will be limited to the associated dates for this product's sales selection. In the example the sales of last year will decline from 200 to 0 after selecting product A. I know it is associative but an enduser will never accept this!
I used to think that these kinds of set analysis expressions are best practices for last year comparisons. However i have my doubts now. A possible solution would be a calendar data island and include the selection in that calendar into the set analysis of each expression i have.
Another solution would be to ignore the selection by extending the set analysis with all possible dimensions i could possibly select in my app.
Both solutions offer a lot of hassle to maintain. So i was wondering if anyone knows a good solution for this problem?
Regards,
Tjeerd
Hi.
The best approach depends on the requirements
You can add month # into calendar and use it for YTD period selection.
The problem with your solution is that actually there is no time selection except year and you try to calculate it dynamically.
I am attaching a sample app that can be helpfull, however I would suggest that you create flags in your reload script to handle this. See Calculating rolling n-period totals, averages or other aggregations
Hi Ioannis and Whiteline,
I still experience the same problem. I can illustrate it better by using the qvw that Ioannis attached.
Please go to the tab 'Set Analysis - Dynamic Advanced'.
Select Salesperson 'Callins, Joan'
Select Year '2007'
Select Months Jan-Feb-Mar-Apr
Please in the table in column (2006 (YTD)' the value 1.589 for product 'Chantell Shirt'
Now select the product 'Chantell Shirt'.
The value is decreased to 463.
I think it is hard to explain the user why this value has decreased because the user didnt directly limit the period selection. He did it indirectly.
How do you guys explain this? Or do you use a workaround?
Regards,
Tjeerd
My preference, in situations like this, is to use function InYearToDate(). Here it would be:
YTD sum(if(InYearToDate(Date, today(), 0), Sales))
LYTD sum(if(InYearToDate(Date, today(), -1), Sales))
If you want to make it independent from some (or all) selections, just use set analysis.
Similar can be used for MTD and QTD.
Additional advantage of this method is that today() can be replaced with any date. Typically I use a variable here.
Regards,
Michael
Hi Michael,
Thanks for the answer.
In my case year and month(s) selections are required. So the inyeartodate() function wont be useful because it cant analyse the excluded data.
'just use set analysis' is the core of the problem i guess. I thought i explained this in my last post. Maybe you missed something? Is my question unclear?
Regards,
Tjeerd
Yes, I've missed something - I've answered your opening question rather than the follow-up.
From the follow up, this specific problem can be resolved this way, see addition in bold:
sum({$<Year = {$(=max(Year)-1)}, Month = {"<=$(=max({<Year={$(=max(Year))},ProductName=>} Month))"}>} LineSalesAmount)
Hope it helps you to resolve other similar issues...
Hi Michael,
Thanks for the quick reply.
For this particular example (chart and selection) it will work. However, in my opening post i suggested the same solution but concluded that it is a hassle to maintain.
This because in theory any dimension selected in the data cloud could cause the same problem.
Well, I still recommend using InYearToDate(). Requirement to select Year and Month can be replaced with setting the base date variable which can be in any year and month. It is simple and comprehensive way. Use {1} if you want to ignore all selections.
The method you prefer is OK. To minimize the hassle, use the same {1} to ignore all selections except explicitly specified.