Discussion board where members can get started with Qlik Sense.
I have issue with grand totals when looking at particular order number. Below is the scenario
Sales $$ Grand Total is looking good but Total Sales $$ by Order is adding all the numbers which is wrong it should show 850$ as well.
And we have filter to one order 123 Grand Total is showing up 1000$ which is it should show only $500.
How do I achieve this?
Go to Solution.
If you don't know this function, you really need to search and learn it. We use it a lot.
It is used in chart and ignore the dimensions.
In above example, if you use sum(total [Sales $$]), you will have 850 for all the rows because it ignore all the dimensions.
If you use sum(total <[Order Number]> [Sales $$]), you ignore all the dimensions except [Order Number]. So the result is the total sales by order numbers.
Have a play and you will understand it
Have you tried to set the total function to sum within the expression?
Yes that what it is messing up things. Tired Auto function as well but no luck.
Wait, so setting it to Sum doesn't give you 850? What does it give you?
it gives $1350 when used Total Function -->Sum
What is the difference between Sales and Total sales expressions?
It would do as its adding (suming) up all the values in your column =1350
I'm not understanding why you need the extra column if it's going to bring through the same values in your sales column?
Perhaps a pivot would be a better way to show the totals you need.
As example I just put some random numbers there ...but Sales $$ column includes commission of the total order amount. Where as Total Sales give me total amount of the Order.
So when they add up at Grand Total both should show same.
Hope I explained it clear !!
Still not sure I understand, but look into using How to use - Dimensionality() here
OK I will try again
Sales $$ = Unit Price * Qty * Comm%
Total Sales $$= Unit Price * Qty
So lets say if order 123 has total sales $500 and it has two sales person who share 40% and 60 % commission spilt based on the Total Order
So they would like to see with Commission Split Sales i.e. Unit Price * Comm% and What is Total Order Sales without Commission.
But the Grand Total for Total Order Sales show match with Commission Split Sales.
Hope I made it clear.