Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))