Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
davidmlachlan
Contributor II
Contributor II

Subtracting one set from another

Hi,

Sorry, this seems simple but cant wrap my head around it.

I have a dimension 'SubType'

Within this dimension, I have a number of items measuring electricity for example [Electricty], [Renewable], [RECs]

I'm trying to create a Piechart that shows the portion of Renewable to Electricity.  For this, i need to subtract the [RECs] from the [Electricity]

 

This attempt of course shows me the proportions of all the data.  

SUM({$<InvoiceSubTypeName={[Electricity],[Renewable],[RECs]},PeriodFinancialYear={'$(vReportingFiscalYear)'}
>} TotalUsage)

 

Do i need to do this before i aggregate it in the set expression? A custom Field perhaps?

 

any advice most welcome

5 Replies
sunny_talwar

Are you saying that you are looking to sum(TotalUsage) where SubType is not RECs? May be this

Sum({$<InvoiceSubTypeName -= {'RECs'}, PeriodFinancialYear = {'$(vReportingFiscalYear)'}>} TotalUsage)
davidmlachlan
Contributor II
Contributor II
Author

You are right in that I do not want to see the [RECs] in the piechart and understand -= will exclude them from the visualisation.

I want to subtract [RECs] from [Electricity] and have the Piechart show the results.

 

 

 

sunny_talwar

This is what I don't understand.... what does subtracting RECs from Electricity even mean? These are some values in a single row? are they within multiple rows? May be you want this?

Sum({$<InvoiceSubTypeName = {'Electricity'}, PeriodFinancialYear = {'$(vReportingFiscalYear)'}>} TotalUsage)
-
Sum({$<InvoiceSubTypeName = {'RECs'}, PeriodFinancialYear = {'$(vReportingFiscalYear)'}>} TotalUsage)

But again, I am not sure what you really mean when you say 'subtracting RECs from Electricity' and may be you won't get what you want using the above expression

 

davidmlachlan
Contributor II
Contributor II
Author

Hi thanks for bearing with me,

SubTypeRECsElectricityRenewable
TotalUsage100400100

 

Consider the above values 

I'd need to subtract [RECS] from [Electricity] and then build a Piechart that will show Electricity= 300 and Renewable =100

 

sunny_talwar

May be this

RangeSum(

  Sum({$<PeriodFinancialYear = {'$(vReportingFiscalYear)'}>} TotalUsage),

  If(InvoiceSubTypeName <> 'Renewable', Sum(TOTAL {$<InvoiceSubTypeName = {'RECs'}, PeriodFinancialYear = {'$(vReportingFiscalYear)'}>} TotalUsage))

)