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

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

jjordaan — Thu, 05 Feb 2015 17:32:15 GMT

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

marcus_sommer — Thu, 05 Feb 2015 20:02:16 GMT

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

jjordaan — Thu, 05 Feb 2015 20:42:40 GMT

Hi Marcus,

Thanks for your reply.

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

jjordaan — Fri, 06 Feb 2015 10:48:36 GMT

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

Fri, 06 Feb 2015 11:01:43 GMT

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

jjordaan — Fri, 06 Feb 2015 11:34:46 GMT

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

Fri, 06 Feb 2015 11:40:58 GMT

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

marcus_sommer — Fri, 06 Feb 2015 11:53:54 GMT

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

jjordaan — Fri, 06 Feb 2015 12:02:00 GMT

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.