[Help!] Compute value based on consecutive month in a quarter

[clingst]

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

[Lynn]

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:

Code Select Expand


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.

Code Select Expand


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)