topic Re: Scripting assistance creating a calculated dimension in QlikView

Scripting assistance creating a calculated dimension

QFanatic — Sat, 16 Nov 2024 17:31:39 GMT

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.

Re: Scripting assistance creating a calculated dimension

edwin — Sat, 06 Feb 2021 17:07:27 GMT

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.

Re: Scripting assistance creating a calculated dimension

edwin — Sat, 06 Feb 2021 17:08:17 GMT

its not letting me add the expressions

Re: Scripting assistance creating a calculated dimension

edwin — Sat, 06 Feb 2021 17:08:44 GMT

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],

Re: Scripting assistance creating a calculated dimension

edwin — Sat, 06 Feb 2021 17:09:08 GMT

in your chart you just sum the measures

Re: Scripting assistance creating a calculated dimension

QFanatic — Mon, 08 Feb 2021 13:18:14 GMT

hi Edwin,

thank you for your reply.

I am not understanding...do you add your 'IF" statements in script?

Thanks

Re: Scripting assistance creating a calculated dimension

edwin — Mon, 08 Feb 2021 19:04:52 GMT

just add the if statements in your load script

Re: Scripting assistance creating a calculated dimension

edwin — Mon, 08 Feb 2021 19:06:55 GMT

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);