Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

sridhar1
New Contributor III

Grand Totals in Straight Table

Hi All,

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

Sales PersonOrder NumberSales $$Total Sales $$ by Order
ABC123200500
XYZ123300500
QWE456100100
WER234250250
Grand Total8501350

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

1 Solution

Accepted Solutions
xufei123
Valued Contributor

Re: Grand Totals in Straight Table

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

15 Replies

Re: Grand Totals in Straight Table

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

Capture.PNG

sridhar1
New Contributor III

Re: Grand Totals in Straight Table

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

Re: Grand Totals in Straight Table

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

sridhar1
New Contributor III

Re: Grand Totals in Straight Table

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

Thanks

Sridhar

Re: Grand Totals in Straight Table

What is the difference between Sales and Total sales expressions?

ogster1974
Honored Contributor II

Re: Grand Totals in Straight Table

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
New Contributor III

Re: Grand Totals in Straight Table

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

Re: Grand Totals in Straight Table

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

sridhar1
New Contributor III

Re: Grand Totals in Straight Table

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.

Community Browser