topic Qlik sense Function similar to SSAS SCOPE in App Development

Qlik sense Function similar to SSAS SCOPE

anwarbham — Sat, 16 Nov 2024 03:29:02 GMT

Hi I have been looking though the qlik functions and i cant seem to find anything that can do a similar job as the Scope Function in SSAS

i have tried APPLYMAP but you cannot in my particular example this wont work.

i have also tried MATCH function Similar issue .

in SSAS i have the data model below that i have replicated PNL_Facts to a PNL hierachy Table "Dimension".

but in SSAS there are a few accounts that are group accounts or calculated accounts.

these accounts do not exists in the PNL_Facts but do exist in the PNL hierachy Table "Dimension"

example in SSAS

SCOPE ([PnL].[PnL line].&[8920000000],{[Measures].[Value Qty],[Measures].[Value Qty Fixed3],[Measures].[Value Qty Fixed4]}); 
this=([PnL].[PnL line].&[3081600000] +[PnL].[PnL line].&[3083116000]); 
End Scope;

Account 8920000000 is an invented account that only exists on the PNL hierachy Table "Dimension" its actually a calculation between 2 accounts 3081600000 + 3083116000 that do exist in the PNL_Facts

the end result is example

Actual FY	Account Code	Type of account No
18.676	3260000000	Real
12.646	8910000000	Invented
-1.76	8920000000	Real
124.19	8930000000	Real
98.84	8950000000	Invented

can this be done in Qlik

the closest thing i have is this

$(vActual_FY(pnl_code ={3081600000}))
+
$(vActual_FY(pnl_code ={3083116000}))

it gets me the total but as a separate column

Re: Qlik sense Function similar to SSAS SCOPE

lorenzoconforti — Thu, 23 Jan 2020 15:34:58 GMT

I think I understand what you are trying to achieve

When you say "it gets me the total but as a separate column", are you trying to implement this at a script level or on the front end? Do you only have one occurrence where you need to merge the two accounts or it's repeated for multiple entries?

It would be good if you could share more details of your Sense data structure and the visualization you are trying to create

Re: Qlik sense Function similar to SSAS SCOPE

anwarbham — Thu, 23 Jan 2020 16:22:48 GMT

i think i have achieved it via a template file i created in excel its not pretty

i created an excel file in the format below with the row labels and account number some account numbers like 3260000000 exist in the PNL Fact table and some like 8920000000 dont 8920000000 is a group of accounts

Row Labels	Account Code	Formula
IAT after Minority Interests	3260000000	0
Operating EBITA	8910000000	1
Divestments net	8920000000	1
EBITA (w/o Divestments)	8930000000	1
Sustainable IBT	8950000000	1
Operating EBITDA	8960000000	1
S-EBITDA	8970000000	1
Total Costs	8980000000	1
IBT before deprec. & amor.	8990000000	1
Marketing, Sales and Distribution costs	9000000000	1
Margin A1	9020000000	1
Cost of Goods Sold variable	9030000000	1
Cost of Goods Sold fix	9040000000	1
Adjusted EBITDA	9050000000	0
Adjusted IBT	9060000000	1

This file loaded into Qlik via the script.

created a QLIk Table visualisation using the [Row Labels] asthe dimension

and the measure is

if( [Account Code] = '8920000000', $(vDivestments_net_Actual_FY), if( [Account Code] = '8980000000', $(vTotal_Costs),$(vActual_FY('')) ))

the variables hold the measures example $(vDivestments_net_Actual_FY)

$(vActual_FY(pnl_code ={3081600000})) + $(vActual_FY(pnl_code ={3083116000}))

i might not get the granularity i want by using the hierarchy table but it works

unless there is a better solution

Re: Qlik sense Function similar to SSAS SCOPE

anwarbham — Thu, 23 Jan 2020 16:36:24 GMT

i think i have achieved it via a template file i created in excel its not pretty

Row Labels	Account Code	Formula
IAT after Minority Interests	3260000000	0
Operating EBITA	8910000000	1
Divestments net	8920000000	1
EBITA (w/o Divestments)	8930000000	1
Sustainable IBT	8950000000	1
Operating EBITDA	8960000000	1
S-EBITDA	8970000000	1
Total Costs	8980000000	1
IBT before deprec. & amor.	8990000000	1
Marketing, Sales and Distribution costs	9000000000	1
Margin A1	9020000000	1
Cost of Goods Sold variable	9030000000	1
Cost of Goods Sold fix	9040000000	1
Adjusted EBITDA	9050000000	0
Adjusted IBT	9060000000	1

This file loaded into Qlik via the script.

created a QLIk Table visualisation using the [Row Labels] asthe dimension

and the measure is

if( [Account Code] = '8920000000', $(vDivestments_net_Actual_FY), if( [Account Code] = '8980000000', $(vTotal_Costs),$(vActual_FY('')) ))

the variables hold the measures example $(vDivestments_net_Actual_FY)

$(vActual_FY(pnl_code ={3081600000})) + $(vActual_FY(pnl_code ={3083116000}))

i might not get the granularity i want by using the hierarchy table but it works

unless there is a better solution