Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

davidmlachlan
New 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

Re: Subtracting one set from another

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
New Contributor II

Re: Subtracting one set from another

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.

 

 

 

Re: Subtracting one set from another

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

 

Highlighted
davidmlachlan
New Contributor II

Re: Subtracting one set from another

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

 

Re: Subtracting one set from another

May be this

RangeSum(

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

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

)