Hi All,
I am having QS containing QItems as follows:
BU
Cust_Name
Product
Location
Year-Month
Revenue
Year-Month contains values as 201102, 201101, 201002,201001 as char data type
Instead of displaying this values in rows,I want to display the same in columns.
It is possible using RS pivot but I also need to calculate Month_LY_Var which is subtraction of (201102-201002)
Is this possible in Framework manager.
Your guidance is appreciated
Thanks in Advance
The easiest solution is probably to model your data as DMR, then you can locate the measure values for the specific date members and subtract them easily.
Regards,
MF.
Hi MFGF,
Thanks for your reply.
Can you give me some startup guidance .
Since I am not having any unique columns nor primary /for keys.
How should I create heirarchies and dim.
Thanks again
So you are taking the approach of putting the Year-Month in columns only because you want to subtract two months ?
If that is the case, you don't need to create it as columns and you can do this in the report instead of doing it in FM
1> Create a Data Item with the calculation -
If ( Year-Month = '201102' ) then (Measure) else (0)
2> Create another Data Item as -
If ( Year-Month = '201002' ) then (Measure) else (0)
3> Create another Data Item as [Data Item1] - [Data Item2]
This will give you the difference.
If you want to make it dynamic then you can use the extract , cast and substring functions to do it.
Ex:
If (
[Year-Month] =
cast(extract(year,current_date),varchar(4)) + cast(extract(month,current_date),varchar(2))
)
then ([Measure] )
else (0)
Hi CognosTechie
Thank you very much for your reply.
Appreciated
a lot
Thanks again