Skip to main content
Announcements
Do More with Qlik - Qlik Cloud Analytics Recap and Getting Started, June 19: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
tom2qlik
Creator
Creator

First Row in Chart Expression returns table value and not Sum

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.

1 Solution

Accepted Solutions
sunny_talwar

Try this:

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

View solution in original post

8 Replies
sunny_talwar

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

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

tom2qlik
Creator
Creator
Author

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
Creator
Creator
Author

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
Creator
Creator
Author

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

Try this:

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

tom2qlik
Creator
Creator
Author

That did the trick.

Thank you!