Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi everyone.
Please find attached.
I have to create a calculated Dimension in my model where I calculate 3 items: (this would be to display in a chart using Calculated Dimension)
1. Value of Debits (would be Sum(val) where Chan = '1SA'). This I would need to calculate for 3 periods - vMaxdate, v1MonthAgo and v2MonthsAgo.
2. Financial Volumes (would be Sum(vol) where Chan = '1SA' and Type = '1'), Also for same 3 periods.
3. Non Financial Volumes (would be Sum(vol) where Chan = '1SA' and Type = '2'), Also for same 3 periods.
if I dont go this route, I'd have to do the long way using Valuelist, and I'm a bit lazy...LOL. I assume, adding these 3 dimensions, would multiply my data volume by 3 - correct?
Please tell me there is an easy way of doing it? All help appreciated.
Thanks.
LOAD
if(chan='1SA', val) as [Value of Debits],
if(chan='1SA' and Type ='1', vol) as [Fin vol],
if(chan='1SA' and Type ='2', vol) as [non Fin vol],
vol,
val,
chan,
sub_channel,
trn_cde,
Category,
JoinKey,
co_cde,
period,
Old_Category,
[CUSTOMER INITIATED],
type
FROM
[$(vQvData)CEO_Report_FinNonFin_Data_$(vReportingDate).qvd] (qvd);
not sure I clearly understand what you want to do, did you want to create 3 new fields based on the value of a field? these become measures not dimensions.
its not letting me add the expressions
if(chan='1SA', val) as [Value of Debits],
if(chan='1SA' and Type ='1', vol) as [Fin vol],
if(chan='1SA' and Type ='2', vol) as [non Fin vol],
in your chart you just sum the measures
hi Edwin,
thank you for your reply.
I am not understanding...do you add your 'IF" statements in script?
Thanks
just add the if statements in your load script
LOAD
if(chan='1SA', val) as [Value of Debits],
if(chan='1SA' and Type ='1', vol) as [Fin vol],
if(chan='1SA' and Type ='2', vol) as [non Fin vol],
vol,
val,
chan,
sub_channel,
trn_cde,
Category,
JoinKey,
co_cde,
period,
Old_Category,
[CUSTOMER INITIATED],
type
FROM
[$(vQvData)CEO_Report_FinNonFin_Data_$(vReportingDate).qvd] (qvd);