Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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