topic Re: Incorrect Sum on Straight Table in QlikView

Incorrect Sum on Straight Table

Thu, 09 Jun 2016 08:58:41 GMT

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

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

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

I have attached sample QVD with data.

Thanks

Re: Incorrect Sum on Straight Table

Kushal_Chawda — Thu, 09 Jun 2016 09:01:59 GMT

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

Re: Incorrect Sum on Straight Table

sunny_talwar — Thu, 09 Jun 2016 09:03:18 GMT

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

Re: Incorrect Sum on Straight Table

Kushal_Chawda — Thu, 09 Jun 2016 09:05:28 GMT

aa gaya tu

Re: Incorrect Sum on Straight Table

sunny_talwar — Thu, 09 Jun 2016 09:05:37 GMT

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)),'##,###,###')

Re: Incorrect Sum on Straight Table

sunny_talwar — Thu, 09 Jun 2016 09:05:51 GMT

Haan bhai aa gaya mein

Re: Incorrect Sum on Straight Table

Thu, 09 Jun 2016 09:20:56 GMT

Sunny is like 'Shaktiman' of QlikView lol

Re: Incorrect Sum on Straight Table

sunny_talwar — Thu, 09 Jun 2016 09:25:12 GMT

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)

Re: Incorrect Sum on Straight Table

Kushal_Chawda — Thu, 09 Jun 2016 09:29:25 GMT

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

Re: Incorrect Sum on Straight Table

sunny_talwar — Thu, 09 Jun 2016 09:30:35 GMT

Jooth maat bol

Re: Incorrect Sum on Straight Table

Thu, 09 Jun 2016 09:34:21 GMT

lol sunny took screenshot and attempted to attach QVW so I thought i should mark you as correct answer compare to Kushal's short answer

but jokes apart.. and thank you both for replying my post.

Re: Incorrect Sum on Straight Table

sunny_talwar — Thu, 09 Jun 2016 09:41:42 GMT

Thanks for recognizing both our efforts

Re: Incorrect Sum on Straight Table

Kushal_Chawda — Thu, 09 Jun 2016 09:46:57 GMT

nahi bhai muje is helful k 50 points milne wale the isliye bola

Re: Incorrect Sum on Straight Table

sunny_talwar — Thu, 09 Jun 2016 09:47:54 GMT

je baat

Re: Incorrect Sum on Straight Table

Kushal_Chawda — Thu, 09 Jun 2016 09:48:37 GMT

Re: Incorrect Sum on Straight Table

Thu, 09 Jun 2016 11:11:18 GMT

May you can earn more points by answering my another question?

How to manage Page Level when field does not exist?

Re: Incorrect Sum on Straight Table

sunny_talwar — Thu, 09 Jun 2016 11:12:53 GMT

I wish I had any experience in NPrinting to help you out

Re: Incorrect Sum on Straight Table

Thu, 09 Jun 2016 12:36:35 GMT

no worries.. may you can answer this question then

Failover/secondary DB connection string