Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !
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.
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
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.
@simonegabrielli What is the expression you are using?
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
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.
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.
Thanks Steve, I'll try with this approach
Hi, did you find the proper solution for this?