Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Incorrect Sum on Straight Table

Hi Guys,

I think Straight Table is not right !

Background:

In my data model, OrderId repeats multiple times. and this is how the data model looks


Data Model.png

What I am trying to do?

I want to see how many products are delivered and how many are shipped


I am using below expressions in straight table

For Total Delivered :  =num(Count(Distinct{$<StatusId={1}>}OrderId),'##,###,###')

For Total Shipped : =num(Count(Distinct{$<StatusId={2}>}OrderId),'##,###,###')


And I get this straight table

Straight Table.png

It looks right but shouldn't the total of delivered product be 4? instead of 2?

I have attached sample QVD with data.

Thanks

1 Solution

Accepted Solutions
sunny_talwar

Try using 'Sum of Rows' as the 'Total Mode'

Capture.PNG

View solution in original post

17 Replies
Kushal_Chawda

I think as you have used DISTINCT that is why it is taking Count as 2 based on expression

try to change expression mode "Sum of rows" instead of expression default

sunny_talwar

Try using 'Sum of Rows' as the 'Total Mode'

Capture.PNG

Kushal_Chawda

aa gaya tu

sunny_talwar

Alternatively, you can use Sum(Aggr()) function, but I really don't prescribe this method

=num(Sum(Aggr(Count(Distinct{$<StatusId={1}>}OrderId), ProductName)),'##,###,###')

=num(Sum(Aggr(Count(Distinct{$<StatusId={2}>}OrderId), ProductName)),'##,###,###')

sunny_talwar

Haan bhai aa gaya mein

Not applicable
Author

Sunny is like 'Shaktiman' of QlikView lol

sunny_talwar

I think Kush responded before me with the same response as mine. He might deserve the correct answer here (unless looking at the image was the only way you could have understood what we were trying to point out )

You can always mark Helpful answers if you find any response to be helpful (mine or kush's in this case)

Kushal_Chawda

lokatech‌  You can mark my answer at least as helpful as I don't mind  not to get the correct

sunny_talwar

Jooth maat bol