Add calculated values in Data Load Editor

[simonegabrielli]

Hy,

I have an Excel table like this:

Customer       KPI                         Country         Amount

01                      Gross Sales         Italy                 500

01                      Net Sales             Italy                  200

02                      Gross Sales         USA                   1000

02                      Net Sales              USA                   600

I have to add a calculated value in the KPI table (Gross Sales + Net Sales) in Qlik Sense. I tried with the master items, but I need a new KPI table  that has the new calculated value inside of it, and not a separate measure.

I load the tables in the data load editor, the calculated value must be added to the KPI Description table:

does anyone have any idea how to add this calculated value in the table in Qlik Sense?

thanks !

[stevedark]

HI @simonegabrielli 

You don't actually need to do this, as doing sum(Amount) will give you the sum of Net and Gross anyway.

You can then get Net or Gross by using Set Analysis, doing sum({<KPI*={'Net Sales'}>}Amount) and sum({<KPI*={'Gross Sales'}>}Amount) respectively.

If you really need separate columns for these in the data model you need to do a GROUP BY when you load from the table:

LOAD
  Customer,
  Country,
  sum(Amount) as [Net Plus Gross],
  sum(if(KPI = 'Net Sales', Amount, 0)) as [Net Sales],
  sum(if(KPI = 'Gross Sales', Amount, 0)) as [Gross Sales]
FROM [lib://library/MyExcel.xlsx]
(ooxml, etc.)
GROUP BY
  Customer,
  Country;

You could do the same without the GROUP BY and the sum statements, and you would have rows with zeros in the Net or Gross columns depending on the type of row it is.

Hope that helps?

Steve

[simonegabrielli]

Hi Steve,

thanks for the help, I really apreciate it. The problem is that I need the three values all togheter in a pivot table, like a Dimensions row. I need to show the Amount for the Net Sales, the Gross Sales and a new value that consist in the sum of both. I tried with the set analysis but does not work.

[Kushal_Chawda]

@simonegabrielli  What is the expression you are using?

[stevedark]

In that case, I think it is just a case of turning on totals for the KPI Description dimension. Find the dimension in the pivot properties and then switch Show Totals to On.

You may then have a problem that typically Gross Sales is the total and Net + Margin = Gross, but that is a different question...

Steve

[simonegabrielli]

Yeah it would work, but the problem is that must be added other calculated values  in the pivot table as dimensions, and therefore it would not be solved. I was hoping indeed to solve with the script in the load editor.

[stevedark · Accepted solution]

So if you want another row with the totals in, in the source table, it would be as follows:

LOAD
  Customer,
  Country,
  KPI,
  Amount
FROM [lib://library/MyExcel.xlsx]
(ooxml, etc.);

CONCATENATE
LOAD
  Customer,
  Country,
  'Net Plus Gross' as KPI,
  sum(Amount) as Amount
FROM [lib://library/MyExcel.xlsx]
(ooxml, etc.)
GROUP BY
  Customer,
  Country;

I would recommend against this approach though, as it really increases your risk of double counting places.

By renaming the Amount column in the second query you could mitigate this. So, say you called it Total instead of Amount you could just have a query in the front end of sum(Amount)+sum(Total).

Hope that helps.

[simonegabrielli]

Thanks Steve, I'll try with this approach 

[Kejti]

Hi, did you find the proper solution for this?