topic Calculate difference between two combined cells. in QlikView

Calculate difference between two combined cells.

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

Hy guys. I`m having a table in which I have sales for each category. For example I have a big category Electronics in wich I have Laptops , SmartPhones , SmartWatches , TV. For each I have a value for sales

Level2	Sales
Laptops	100
SmartPhones	200
SmartWatches	150
TV	50

And I have another table for the budget of 2016 for each Category. But the problem is that in the budget table I`m having something like this

Level2	Budget
Laptops	120
SmartPhones	380
SmartWatches	-
TV	70

In this table I have the budget for SmartPhones and SmartWatches combined.

I would like to add an expression to calculate the percentage achieved for all Categories such as :

Level2	Sales	Budget	Percentage
Laptops	100	120	(100-120)/100
SmartPhones	200	380	((200+150)-380)/100
SmartWatches	150	-	-
TV	50	70	(50-70)/100

As you can see , I want to combine in the expression those rows that have value SmartPhones and SmartWatches for the percentage calculation. Is there any way I can do that?

Thank you

Re: Calculate difference between two combined cells.

razvan_brais — Tue, 04 Oct 2016 09:21:12 GMT

This is the expression that i`m writing :

=if(WildMatch(LEVEL2,'SmartPhones'),

(sum({<DATE={'$(=GetFieldSelections(DATE))'},LEVEL2={'SmartPhones','SmartWatches'}>} SALES)/1000000),

if(WildMatch(LEVEL2,'SmartWatches'),0,(sum({<DATE={'$(=GetFieldSelections(DATE))'}>} SALES)/1000000 )))

But at the smartPhones row in Percentage column it brings only the value from the smartphones row.

Re: Calculate difference between two combined cells.

sunny_talwar — Tue, 04 Oct 2016 15:42:07 GMT

May be try like this:

If(Match(LEVEL2, 'SmartPhones'), Sum(TOTAL{<LEVEL2={'Smart*'}>} SALES)/1000000,

If(Match(LEVEL2, 'SmartWatches'), Null(), Sum(SALES)/1000000))

Re: Calculate difference between two combined cells.

sunny_talwar — Tue, 04 Oct 2016 15:43:21 GMT

Or this if you have more LEVEL2 with the word Smart in them:

If(Match(LEVEL2, 'SmartPhones'), Sum(TOTAL{<LEVEL2={'SmartPhones','SmartWatches'}>} SALES)/1000000,

If(Match(LEVEL2, 'SmartWatches'), Null(), Sum(SALES)/1000000))