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

[sfatoux72]

In your IF, you use : Month<='3'       <== numeric value

And in your Set Analysis, you use :  Month={'Jun'}      <== string value

Are you sure that your field Month is Dual. If it's not the case, it could explain the comportement you describe.

[#3]

Sample QVW

[#4]

Just attached the sample QVW

[swuehl]

If you are only using Year as dimension, not Month, you can't have your condition check if the Month field value is smaller or larger than a number, this condition will always be evaluated to FALSE, since there is no unique Month value to compare to.

See

Use Aggregation Functions!

[#6]

I did add month to the dimension but same result. Can you pleas look at the sample QVW I have attached to the original thread and let me know if you can find something

[maxgro]

if you want 4 expression for quarters I think you can modify your exp

If(Month <='3',

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

Sum({<Year={$(=Max(Year)-1),$(=Max(Year)-2),$(=Max(Year)-3)},Month={'Mar'}>} Amount))

with

Sum({<Month={'jan','feb','mar'},Year={$(=Max(Year)-1),$(=Max(Year)-2),$(=Max(Year)-3)}>} Amount)

[#8]

Doest work, can u pls attach sample qvw if u have.  Please keep in mind that Mar is cumulative of Jan, Feb and March, so cant do jan feb and march

[sfatoux72]

Try with ‌:

if( max(month) <= 3,

...

[swuehl]

You need to apply a set expression also to your Month comparison in the conditon, to allow QV to evaluate the Month also for the years you haven't selected (but these are the only years you are showing):

If(Only({<Year>} Month) <= 3,

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

Sum(TOTAL<Year> {<Year={$(=Max(Year)-1),$(=Max(Year)-2),$(=Max(Year)-3)},Month={'Mar'}>} Amount))

If you are using a Month dimension, you would also need a TOTAL<Year> qualifier in your else branch, otherwise the projection of this aggregation (of March's Amount)  to Month > 3 will be zero.

Also, you will get two lines for a Month selection <3.

But I assume you want to remove the Month dimension anyway (as I read your OP), then the result should now be correct (assuming you are using always one selected value in Month field).