Qlik Community

Ask a Question

New to QlikView

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

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
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
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.

May be try like this:

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

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

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