First Row in Chart Expression returns table value and not Sum

[tom2qlik]

Hi All,

I am trying to show the sum of people in a chart against every date which the key was recorded against in the database.

The Key is Code - CodeDate - GroupNo (2AIKG|29/08/2006|8)

It is working fine except for two rows in the chart.  The first row and a row in the middle.

The chart looks like this.

As you can see the first line in the chart shows 3000 for recorded Date 29/08/2006 however I need to it show 12002 for that date.

Below is the data to show what the original looks like in a regular table.

I did make another chart adding in the rank number to the chart for an explanation.

So from what I understand the sum expressions 'Sum(People)' is summing on Code, CodeDate, GroupNo and Rank however I only want it to sum on Code, CodeDate and GroupNo. (Basically sum the middle table of this post for every recorded date).

Any ideas?

Thanks in advance.

[sunny_talwar]

What is the expression that you used? Is this what you are using?

Sum(TOTAL <Code, CodeDate, GroupNo> People)

[tom2qlik]

The first table in the post above is simply sum(people).

I have tried sum(aggr(SUM(DISTINCT people), Code, CodeDate, GroupNo, Rank)) however it only returns a single row as shown below.

[sunny_talwar]

Can you try this:

Sum(TOTAL <Code, CodeDate, GroupNo> People)

or this

Aggr(NoDistinct Count(People), Code, CodeDate, GroupNo)

[tom2qlik]

This expression -

Sum(TOTAL <Code, CodeDate, GroupNo> People)

Produces this:

Which I believe is 12002 (Table 2 in original post) * Number of rows for Recorded Date.

And this expression -

Aggr(NoDistinct Count(People), Code, CodeDate, GroupNo)

Produces this:

[sunny_talwar]

Sorry, I meant

Aggr(NoDistinct Sum(People), Code, CodeDate, GroupNo)

But the above won't work as well... Will it possible for you to share a sample to look at the issue?

[tom2qlik]

Yes of course.

Attached.

The reason I have written the script like it is, is because I want to create accumulative fields later on.

Thanks,

[sunny_talwar · Accepted solution]

Try this:

Sum(TOTAL <Code, CodeDate, GroupNo> Aggr(Test_One.people, Code, CodeDate, GroupNo, Test_One.Rank))

[tom2qlik]

That did the trick.

Thank you!