Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Calculate difference between two combined cells.

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

Level2Sales

Laptops

100
SmartPhones200
SmartWatches150
TV50

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

Level2Budget
Laptops120
SmartPhones380
SmartWatches-
TV70

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 :

Level2SalesBudgetPercentage
Laptops100120(100-120)/100
SmartPhones200380((200+150)-380)/100
SmartWatches150--
TV5070(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

3 Replies
Highlighted
Creator III
Creator III

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.

Highlighted

May be try like this:

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

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

Highlighted

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))