topic Re: Addition and Subtraction of fields to form a new field in QlikView

Addition and Subtraction of fields to form a new field

amijit_hazarika — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have created a column which will show 4 revenue values. I am doing it in script.

With all conditions fulfilled I was able to get the 1st three fields;

1. Total Part Revenue = If(Geo = 'Coast', 'Total Part Revenue') as Total_Measure

2. Total Revenue from Outside = If(Geo <> 'Coast', 'Total Revenue from Outside') as Total_Measure

3. Less Revenue = If(Geo <> 'Coast', Region = 'Aus', 'Less Revenue') as Total_Measure

4. Overall Revenue = 1st Field + 2nd Field - 3rd Field.

I am struggling to get the 4th field. Any input would be of great help.

Total_Measure	Revenue
Total Part Revenue	150
Total Revenue from Outside	70
Less Revenue	20
Overall Revenue(1st Field + 2nd Field - 3rd Field)	200

stalwar1‌ stevedark‌

Thanks

Re: Addition and Subtraction of fields to form a new field

qlikviewwizard — Tue, 05 Jun 2018 09:56:35 GMT

Hi,

Can you attach the sample data in text file?

Re: Addition and Subtraction of fields to form a new field

amijit_hazarika — Tue, 05 Jun 2018 09:58:45 GMT

Hi,

My Organisation doesnt allow me to upload any data. However I have showed the sample possible data in the above question.

Thanks

Re: Addition and Subtraction of fields to form a new field

qlikviewwizard — Tue, 05 Jun 2018 09:58:57 GMT

Use RangeSum(Total Part Revenue,Total Revenue from Outside,Less Revenue)

Hope this will help you.

Re: Addition and Subtraction of fields to form a new field

amijit_hazarika — Tue, 05 Jun 2018 10:07:44 GMT

Hi Arjun,

But I need

Total Part Revenue + Total Revenue from Outside - Less Revenue.

Rangesum() is not helping me in minus.

Re: Addition and Subtraction of fields to form a new field

shiveshsingh — Tue, 05 Jun 2018 10:32:45 GMT

Try like this

=RangeSum(Column1,Column2,-Column3)

Re: Addition and Subtraction of fields to form a new field

sunny_talwar — Tue, 05 Jun 2018 11:13:20 GMT

This is something you are looking to do in the script or front end of the app?

Re: Addition and Subtraction of fields to form a new field

jyothish8807 — Tue, 05 Jun 2018 11:50:23 GMT

How did you map the revenue to the corresponding dimension values ?

Re: Addition and Subtraction of fields to form a new field

stevedark — Sat, 09 Jun 2018 16:00:27 GMT

Your best bet is probably going to be calculating it in the front end, but if you do want to do it in script you need to do the load from the data twice, with a concatenate, i.e.

Revenue:

LOAD

Revenue,

... your if statements here ... as Total_Measure

FROM ... your data source ...

;

CONCATENATE (Revenue)

LOAD

Revenue,

'Overall Revenue' as Total_Measure

FROM ... your data source ...

;

The massive risk and disadvantage of this approach is that all of the revenue will be double counted, so if you do a sum(Revenue) in the front end, without Total_Measure as a dimension you will get wrong values.

Not doing the concatenate, and having each value once would remove this risk. You can then use the Total feature in Sense tables to show a totals row.

Hope that helps.

Steve