Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

Total or aggr not working like it should

Hi

I have done this before and it worked like a charm, but for some reason my expressions are not working like they should this time around.

I created a band that seggregates the amounts into the various categories based on the year. These values were deflated to show a more realistic amount, and then categorized with the dimensions. Now, when I try to produce a percentage based on the column totals within the expressions it does not want to work.

See Results below of what I am obtaining/ This happens for all the year. I've only shown for the one:

2009
Bandcount(RecordNumber)sum( TOTAL <Year> RecordNumber)aggr(sum(RecordNumber),Year)
R0-R100K326326
R101-R200K20962096
R201-R300K2576257611964
R301-R400K24782478
R401-R500K15061506
R501-R750K16841684
R751K +12981298


I would like to take the count(RecordNumber) and divide it by the total of the row.

Thanks

1 Solution

Accepted Solutions
Not applicable

Re: Total or aggr not working like it should

Thanks Brenner. That doesn't work as count(total Recordnumber) sums the values for all the records in the table and not just by each year (column).

However, I obtained the answer on twitter from @countnazgul:

"use aggr(NoDistinct sum( ... to have the total number in all rows"

The reason for using NoDistict as per @countnazgul is that "aggr return only 1 num - total for whole year.nodistinct spreads this num across all rows.distinct is ON by default for aggr"

Hope this helps others out there.

2 Replies
Partner
Partner

Total or aggr not working like it should

Hi,

you need only to enter:

Count (RecordNumber)/Count (Total RecordNumber

Not applicable

Re: Total or aggr not working like it should

Thanks Brenner. That doesn't work as count(total Recordnumber) sums the values for all the records in the table and not just by each year (column).

However, I obtained the answer on twitter from @countnazgul:

"use aggr(NoDistinct sum( ... to have the total number in all rows"

The reason for using NoDistict as per @countnazgul is that "aggr return only 1 num - total for whole year.nodistinct spreads this num across all rows.distinct is ON by default for aggr"

Hope this helps others out there.