Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculate specific rows in dimension from load script?

Hi all,

 

I have a excel file with the following structure (see attached) that I use as a datasource. By sorting the rows this way I get the right calculations and structure in my pivot table. My problem is that in some rows I want to make calculations using the the values from other rows. Is there a way I can accomplish that either in the excel sheet or in the load script?

 

 

1 Reply
petter
Partner - Champion III
Partner - Champion III

The calculations can be done either in the load script or very similarly in the charts instead. I have made an example app that does both.

XL:

LOAD

    Headline,

    If( IsNull(Account) , ' ' , Text(Account)) AS Account,

    If( IsNull([Cost center]),' ' , Text([Cost center])) AS [Cost center],

    Trim([Calculation wanted]) AS C

FROM [lib://Downloads/Example financial report.xlsx]

  (ooxml, embedded labels, table is [Financial report])

WHERE

  RecNo()<=21;

// Give each row a name which is the same as the headline

FOR i=1 TO NoOfRows('XL')

  var = Peek('Headline',i-1);

    [$(var)] = i-1

NEXT

LOAD

    Headline,

    Account,

    [Cost center],

    If( RecNo() = 1 , 1 , If( Left( C , 1 ) = '=' , Peek('C#')+1 , Peek('C#') ) )  AS C#,

    If( Left(C,1) <> '=' , C , Pick( Peek('C#') ,

  RangeSum(Peek('C',$(Sales)),Peek('C',$(Net Shipping Revenue)),Peek('C',$(Other Sales)))

        ,RangeSum(Peek('C',$(COGS)),Peek('C',$(Other COGS)))

        ,RangeSum(Peek('C',$(Net Revenue)),Peek('C',$(Total Cogs)))/Peek('C',$(Net Revenue))

        ,Peek('C',$(Total Cogs))/Peek('C',$(Sales))

        ,Peek('C',$(Net Revenue))+Peek('C',$(Total Cogs))

        ,Peek('C',$(CM1))/Peek('C',$(Net Revenue))

        ,RangeSum(Peek('C',$(Delivery Costs)),Peek('C',$(Payment Cost)),Peek('C',$(Pick&Pack Personnel Costs)),Peek('C',$(Ops Personnel Costs)),Peek('C',$(Customer Care Personnel Costs)),Peek('C',$(Memnon)),Peek('C',$(Material)))

  ,RangeSum(Peek('C',$(CM1)),Peek('C',$(CM2 Cost)))

        ,Peek('C',$(CM2 Total))/Peek('C',$(Net Revenue))

))

    AS C

RESIDENT

  XL

;

DROP TABLE XL;

The calculations in the chart would be as an alternative:

If( Len([Cost center]) > 1 , Sum(C) , Pick( Above(TOTAL C#) 

  ,RangeSum(Top(TOTAL Sum(C),$(Sales)+1),Top(TOTAL Sum(C),$(Net Shipping Revenue)+1),Top(TOTAL Sum(C),$(Other Sales)+1))

  ,RangeSum(Top(TOTAL C,$(COGS)+1),Top(TOTAL C,$(Other COGS)+1))

        ,RangeSum(Top(TOTAL C,$(Net Revenue)+1),Top(TOTAL C,$(Total Cogs)+1))/Top(TOTAL C,$(Net Revenue)+1)

        ,Top(TOTAL C,$(Total Cogs)+1)/Top(TOTAL C,$(Sales)+1)

        ,Top(TOTAL C,$(Net Revenue)+1)+Top(TOTAL C,$(Total Cogs)+1)

        ,Top(TOTAL C,$(CM1)+1)/Top(TOTAL C,$(Net Revenue)+1)

        ,RangeSum(Top(TOTAL C,$(Delivery Costs)+1),Top(TOTAL C,$(Payment Cost)+1),Top(TOTAL C,$(Pick&Pack Personnel Costs)+1),Top(TOTAL C,$(Ops Personnel Costs)+1),Top(TOTAL C,$(Customer Care Personnel Costs)+1),Top(TOTAL C,$(Memnon)+1),Top(TOTAL C,$(Material)+1))

  ,RangeSum(Top(TOTAL C,$(CM1)+1),Top(TOTAL C,$(CM2 Cost)+1))

        ,Top(TOTAL C,$(CM2 Total)+1)/Top(TOTAL C,$(Net Revenue)+1)

))

If you prefer to do calculations in the chart you could remove lines 18 to 39 in the load script.

2017-03-10 02_54_31-Qlik Sense Desktop.png