Solved: Re: Grand Totals in Straight Table - Qlik Community

sridhar1 · ‎2018-02-15

Hi All,

I have issue with grand totals when looking at particular order number. Below is the scenario

Sales Person	Order Number	Sales $$	Total Sales $$ by Order
ABC	123	200	500
XYZ	123	300	500
QWE	456	100	100
WER	234	250	250
Grand Total		850	1350

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?

Thanks

Sridhar

NZFei · ‎2018-02-15

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

Fei

View solution in original post

sunny_talwar · ‎2018-02-15

Have you tried to set the total function to sum within the expression?

sridhar1 · ‎2018-02-15

Yes that what it is messing up things. Tired Auto function as well but no luck.

sunny_talwar · ‎2018-02-15

Wait, so setting it to Sum doesn't give you 850? What does it give you?

sridhar1 · ‎2018-02-15

it gives $1350 when used Total Function -->Sum

Thanks

Sridhar

sunny_talwar · ‎2018-02-15

What is the difference between Sales and Total sales expressions?

ogster1974 · ‎2018-02-15

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.

sridhar1 · ‎2018-02-15

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

sunny_talwar · ‎2018-02-15

Still not sure I understand, but look into using How to use - Dimensionality() here

sridhar1 · ‎2018-02-15

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.