Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Creating P&L and Balance Sheet Reports using MDX

Hi QV community,

I am working a project where I need to build P&L/BS reports in QV. I need to achieve this through MDX. I was able to successfully load information into pivot/straight tables. But as you know pivot does not work for this type of reports.

Our hierarchies spans across different levels, therefore I am forced to create a mapping table that will help QV identify & group accounts into specific categories. 

I am struggling with running 'intervalmatch' statement by combining QV syntax with MDX . My query simply fails.

Does anyone have any suggestions or experience with building hierarchies using MDX & QV?

Any other suggestions on how to go about this?

Please help!

PLTemp:

SELECT

  { null } on 0,

        ([Account].[H1].[LEV14])on 1

FROM [Finance];

SELECT

{ null } on 0,

([Account].[H1].[LEV14]) on 1

FROM [Finance];

IntervalMatchTemp:

INTERVALMATCH ("[Account].[H1].[LEV14]") LOAD [GL Account Start], [GL Account End] RESIDENT Layout;

IntervalMatch:

Load

distinct

("[Account].[H1].[LEV14]"),

[GL Account Start] & '|' & [GL Account End] as GLStartEnd

resident

IntervalMatchTemp;

drop table IntervalMatchTemp;