Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Attached a version that also works in with Month dimension.
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
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
...
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
load
Year(Date) as Year,
Month(Date) as Month,
'Q' &Ceil((month)/3) as quarter
use this code it useful to you