Straight Table Quarter Issue

[#1]

I have a straight table with Year as a Dimension.  I am using Q1,Q2,Q3 and Q4 as quarters and generatiing the quarters with the 4 Expressions as Q1,Q2, Q3 and Q4

Q1

If(Month<='3',

Num((Sum({<Year={$(=Max(Year)-1),$(=Max(Year)-2),$(=Max(Year)-3)},MPL_Flag={'1'}>} Total_Incurred) ) ,'$#,##0'),

Num((Sum({<Month={'Mar'},Year={$(=Max(Year)-1),$(=Max(Year)-2),$(=Max(Year)-3)},MPL_Flag={'1'}>}Total_Incurred) ) ,'$#,##0'))

Q2

If (Month <='3','0',If(Month >'3' and Month <= '6',

Num((Sum({<Year={$(=Max(Year)-1),$(=Max(Year)-2),$(=Max(Year)-3)},MPL_Flag={'1'}>} Total_Incurred) ) ,'$#,##0'),

Num((Sum({<Month={'Jun'},Year={$(=Max(Year)-1),$(=Max(Year)-2),$(=Max(Year)-3)},MPL_Flag={'1'}>}Total_Incurred) ) ,'$#,##0')))

and so on

But looks like the table is always evaluating a static number  using expressions, its ignoring  for example

If I select Feb , Q1 still gives me

Num((Sum({<Month={'Mar'},Year={$(=Max(Year)-1),$(=Max(Year)-2),$(=Max(Year)-3)},MPL_Flag={'1'}>}Total_Incurred) ) ,'$#,##0'))

If I select  Apr, Q2 still give me

Num((Sum({<Month={'Jun'},Year={$(=Max(Year)-1),$(=Max(Year)-2),$(=Max(Year)-3)},MPL_Flag={'1'}>}Total_Incurred) ) ,'$#,##0')))

What shall I do so that when I  select  Jan or Feb or March I shall get numbers for Jan , Feb and March in Q1 column

[swuehl]

Attached a version that also works in with Month dimension.

[#3]

I am sorry I thought its working , but when I was testing it realized  there is a row with '0' for Q1 and Q2 I dont get that row, cuz I am supresssing 0 values, but if I un supress 0 values I get all years not just (-1,-2 and -3). Am I  missing something. I would like to show the 0 values  only for the years requested

[jonathandienst]

If you want to analyse the data by quarters, you should include quarters in your model as a derived field. Since you deleted the load script from the sample you uploaded, I can only give you guidelines, but assuming that Month is coming from your data source and that Month is a dual value:

LOAD

     ...

     If(Month <= 3, Dual('Q1', 1),

          If(Month <= 6, Dual('Q2', 2),

          If(Month <= 9, Dual('Q3', 3), Dual('Q4', 4)))

     ) As Quarter,

     ...

Now you can use Month selection(s) and dimensions to view the results of selected months and Quarter selections or dimensions to view the data by quarter. Your expression becomes simply:

     =Sum(Amount)               // for the selected year(s)

     =Sum({<Year = {"$(=Max(Year))"}>} Amount)      // for the selected year

     =Sum({<Year = {"$(=Max(Year)-1)"}>} Amount)    // for the prior year

     ...

[jonathandienst]

If you want data for the last 3 years:

=Sum({<Year = {">=$(=Max(Year)-3) <=$(=Max(Year))"}>} Amount)  

or

=Sum({<Year = {">=$(=Max(Year)-3) <=$(=Max(Year)-1)"}>} Amount)  

depending on your exact requirements

[Anonymous]

load

Year(Date) as Year,

Month(Date) as Month,

'Q' &Ceil((month)/3) as quarter

use this code it useful to you