I have a problem to compute a value based on the measure value of a consecutive month in a quarter.
For example:
A sales person only entitled for a quarterly commission of 500 if the revenue is more than 1000 for EACH month in a quarter.
This report will be generated at every beginning of a quarter.
Eg: At 1/10/2015, this report will compute the quarterly commission for Q3 - Last Quarter.
I am working on a relational package.
Dimension are Sales Person, Tier Date.
Measure are Revenue
My initial attempt is to create a Data Item as following logic:
if ([First Month Revenue] > 1000 AND [Second Month Revenue] > 1000 AND [Third Month Revenue] > 1000)
then (500)
else (0)
However, I couldnt get the correct [First Month Revenue]
What I have done is:
[First Month] : to_char(_add_months(current_date, -1), 'YYYY-MM') /*At this moment for testing purpose I put -1, It will be changed to -3 after report is done*/
[First Month Revenue] : total (revenue for [Sales Person], [First Month])
But my [First Month Revenue] is always the total of WHOLE Quater.. not the First Month of the Quarter..
Assume that for a Sales Person Benny has a revenue of
200 at July
400 at August
but the [First Month Revenue] is always 600 but not 200.
I think i am using the total wrongly
Please advice and appreciate if there is a better approach
I don't see where you've used Tier Date in determining the first month sales. Your expression for first month will yield the same thing for every single row in the result set because it is based on current date minus one month. That is why your sales are always showing the total for all the months....you are not comparing your derived date to the date applicable for the revenue.
Perhaps you just need a First Month Revenue query item with an expression something like this:
case
when to_char(_add_months( [Tier Date Month], -3), 'YYYY-MM') = to_char(_add_months(current_date, -3), 'YYYY-MM') then [Revenue]
else 0
end
And similarly for second and third month query items. Then compare each to the required threshold of 1000 within your commission query item.
if ( total ( [First Month Revenue] for [Sales Person] ) > 1000
AND
total ( [Second Month Revenue] for [Sales Person] ) > 1000
AND
total ( [Third Month Revenue] for [Sales Person] ) > 1000
)
then (500)
else (0)