8 Replies Latest reply: Feb 6, 2015 7:02 AM by Jeroen Jordaan

# Compare sum of rows of column 1 with sum of rows column 2 in straight table with dimension limits

Hi everyone,

I'm stuck with probably something stupid.

I have created a straight table with the dimension limits option.

I want to show the top 10 (can be changed through a variable) with the total of the top 10 but also the total of the whole / total.

So far so good. I have added a screenshot below.

The problem that I have is that for the total of the whole / total I use the 'Show total' option in the Dimension Limits

and for the total of the Top 10 I use the Total Mode 'Sum of Rows' of the expression.

But for the column Index LYTD (which is a comparison of sales with Sales LY * 100) the calculation is correct for the whole / total but not for the Top 10. In my screenshot I use the Total Mode 'Expression Total' (which is not correct) but I cannot use Sum or Average of Rows.

The outcome must be 172.

I hope someone can help.

Thank you in advance

• ###### Re: Compare sum of rows of column 1 with sum of rows column 2 in straight table with dimension limits

I assume you will need some kind of calculated aggregation with aggr() and/or the TOTAL-qualifier instead the Total-Mode of the table, see here various examples:

http://community.qlik.com/search.jspa?q=straight+table+total+aggr&type=document

- Marcus

• ###### Re: Compare sum of rows of column 1 with sum of rows column 2 in straight table with dimension limits

Hi Marcus,

I cannot see how I can combine the total of the whole with the total of the Top 10 in one column of the straight table.

Do you have an idea?

• ###### Re: Compare sum of rows of column 1 with sum of rows column 2 in straight table with dimension limits

I hope someone has an idea

• ###### Re: Compare sum of rows of column 1 with sum of rows column 2 in straight table with dimension limits

Hi,

I think you can use if statement like

if(rowno()=0, your custom expression to get outcome of 172 ,Index LYTD) in your expression.

rowno()=0 will check whether the current row is total row or not

Regards

Nandkishor

• ###### Re: Compare sum of rows of column 1 with sum of rows column 2 in straight table with dimension limits

Hi Nandkishor,

Thanks for your idea but unfortunately is does not work.

I think it doesn't identify de total of the top 10 as RowNo 0

• ###### Re: Compare sum of rows of column 1 with sum of rows column 2 in straight table with dimension limits

Can u provide me the sample qvw and expression so that I can try it out

• ###### Re: Compare sum of rows of column 1 with sum of rows column 2 in straight table with dimension limits

nandkishor pandirkar is right you will need to use an expression which queries the rowno and the differ the calculation. Important is that you couldn't use the inbuild qv-functionalities for top - you will have to create this with a calculated dimension with hidden null-values like:

= if(aggr(rank(sum({< Year = {2013}>} Sales)), item)<=10, Item)

and then use expressions like:

if(rowno() = 11, sum({1< Year= {2014}>} total Sales),
sum({< Year= {2014}>} Sales)) /
if(rowno() = 11, sum({1< Year= {2013}>} total Sales),
sum({< Year = {2013}>} Sales))

Of course you could make these expressions with Year = {"\$(=max(year)-1)"} and/or variables for rowno() and rank dynamically.

- Marcus

• ###### Re: Compare sum of rows of column 1 with sum of rows column 2 in straight table with dimension limits

Hi Nandkishor,

I said that your RowNumber idea didn't work but I did something wrong so it did work.

Thank you very much for your help.

@Marcus

Thank you also for your help.