Discussion Board for collaboration related to QlikView App Development.
I have a straight table, with Customer dimension and Sum(Sales) as expression.
I want another Expression that should show me that Customer sales as a % of Total Sales. I am unable to figure out how to do that. Any help is appreciated.
I tried Sum( total Sales) but that doesnt give right answer as the total takes all the values in the database and not only the ones I have selected in my table. For example, my database looks like this:
In my dimension, I want to show only the negative sales.
|Total -ve Sales||-100|
Then I want to show % for each of the rows against -ve sales.
Sum(Total Sales) is giving me -70, while I want the total for only the rows selected in my dimension. I hope I am able to ask the question correctly.
To create the % column, do as Abey said above. If you want to see the relative % (i.e. of Total Sales, Customer A Contributes x %, etc.), select the "Relative" checkbox in your expressions tab of your straight table properties.
Hope this helps.
I'm not too sure, I've never attempted something similar. I would suspect it might be a bit easier if you created the field in the load script, and then used that field in your slider.
Hope this steers you in the right direction.
I attempted something on these line. I created a variable and then created a slider that changes this variable. The range of the variable is 0% to 100%.
Now, I want to show the above mentioned table but only those dimensions that are selected in the slider:
So, if the slider is selected at 40%, I want to filter the dimension to show only Customer D.
One way of doing it, and I'm not sure if this is what you're after, is having an IF statement in the Expression of the Sales Column, i.e.:
Sum(IF((Sum(Sales) / Sum(TOTAL Sales)) >= $(yourVariable), Sales)
This would give you the following scenario: If a user selects 40% for the slider, D will show as requested. If a user selects 20%, B, C and D should be shown.
The problem with Sum(Total Sales) is that it does a total on all the rows present in the database, and not only the ones that I have selected in the dimension. So, for example, if the Customers shown above are only from 1 region (i have specified an IF clause in dimension), the Sum(Total Sales) will show all sales from all regions. Is there a way to get Total Sales only for the Customers present in the dimension? If yes, then I can implement your above recommendation.
I am attaching a test file, where I have tried to implement Sum(Total Sales). As you can see, it is giving incorrect value. Please suggest a way so that I can use my slider to show only the relevant values in my straight table.