Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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!