topic Re: How to indicate last row in pivot table chart type? Version 2 in QlikView

How to indicate last row in pivot table chart type? Version 2

Fri, 23 May 2014 09:37:52 GMT

Hello guys,

Last time I posted to find a way to get the last row in a pivot table chart type as shown below

I got an answer in which the expression is as follows:

if(rank(total RowNo(total)) = 1, 'L')

That definitely was the correct expression but it was silly of me that I failed to notice this particular condition; while it correctly displayed the last row in the pivot table for a selected country, but when you select multiple country it will still indicate the last row of the pivot table and this is quite not the result I desire.

What I wanted is to show it like this

How do I tweak the expression so that it will display at the last row of each country selected and displayed in the pivot table? As pictured above?

Thank you for your kind attention guys

Re: How to indicate last row in pivot table chart type? Version 2

Fri, 23 May 2014 09:54:23 GMT

Hi,

if you need inter record, try:

if(Below(Country)<>Country,'Last')

Re: How to indicate last row in pivot table chart type? Version 2

Fri, 23 May 2014 10:14:04 GMT

Hello Dariusz,

Thank you for your kind attention, unfortunately this is the result that I got

Almost there but not quite yet. Do you know of any other way?

Again thank you for your time answer my question

Re: How to indicate last row in pivot table chart type? Version 2

preminqlik — Fri, 23 May 2014 10:17:49 GMT

try this ,
if(row(total)=noofrows(),'Last',null())

Re: How to indicate last row in pivot table chart type? Version 2

preminqlik — Fri, 23 May 2014 10:21:28 GMT

sorry try this

if(rowno(total)=noofrows(total),'Last','')

Re: How to indicate last row in pivot table chart type? Version 2

Fri, 23 May 2014 10:25:00 GMT

Hello Prem,

Thank you for the suggestion but unfortunately this is the result

In which is similar to the result I got in the first place.

Do you have any other suggestion to tackle this problem?

Thank you so much for your kind attention

Re: How to indicate last row in pivot table chart type? Version 2

jagan — Fri, 23 May 2014 10:35:35 GMT

Hi,

Try like this

=If(Country <> Below(TOTAL Country ,1), 'Last', '-')

Replace Country with your Country Dimension name.

Regards,

Jagan.

Re: How to indicate last row in pivot table chart type? Version 2

Fri, 23 May 2014 10:45:17 GMT

Hello Jagan,

Thank you very much for your suggestion but this is the result that I get

The result is almost as intended, we just need to figure out to show only Last on the last row of the Country only and not Country / Year last row.

Do you think there is any other way to solve this?

Thank you so much for your time taken to answer my question.

Re: How to indicate last row in pivot table chart type? Version 2

preminqlik — Fri, 23 May 2014 10:59:48 GMT

hi try this

if(rowno()=count(total <Country>aggr(rowno(),Country,PostingYear,PostingMonth,Days)),'Last','')

Re: Re: How to indicate last row in pivot table chart type? Version 2

jagan — Fri, 23 May 2014 11:02:11 GMT

Hi,

It is working for my sample data. Please refer attached file.

Regards,

Jagan.

Re: Re: Re: How to indicate last row in pivot table chart type? Version 2

Fri, 23 May 2014 11:17:26 GMT

Hi Jagan,

Hmm it is working on yours but not mine.. really weird. And also silly me for not sharing the qvw file earlier.

Maybe you can have a look for me please?

Thank you so much for your kind attention

Re: Re: Re: How to indicate last row in pivot table chart type? Version 2

Fri, 23 May 2014 11:44:43 GMT

Please check this one:

=If(Country <> Below(TOTAL Country,1) and not IsNull(Country), 'L', '-')

Re: Re: Re: How to indicate last row in pivot table chart type? Version 2

Fri, 23 May 2014 12:31:29 GMT

Hi Dariusz,

It works! Thank you very much for you kind attention

Thank you again