Author Topic: [SOLVED] Filter dimention based on last row with data for last column  (Read 219 times)

Offline qvixote

  • Community Leader
  • *****
  • Join Date: Jul 2013
  • Posts: 108
  • Forum Citizenship: +1/-0
Hello!

I'm making a report over a Transformer PowerCube, and I have a crosstab with YEARS on columns and WEEKS on rows (none of those dimentions are time dimention). Please see attached image. As you can see, year 2018 have data only till week 20.

I need to make a cross table like this one, with those 3 years on columns (they are filtered like [año] in ([2016];[2017];[2018] and that's ok) and only 3 weeks on rows, and those weeks must be the last 3 weeks with data on year 2018, i.e. in the data of the image, the table must show only weeks 18, 19 and 20, but I can't put a fixed filter as I did with years, because it have to change dinamically every week.

How can I filter something like that?

Thanks.
« Last Edit: 16 May 2018 01:39:27 pm by qvixote »

Offline Reinhard

  • Community Leader
  • *****
  • Join Date: Nov 2017
  • Posts: 135
  • Forum Citizenship: +1/-0
Re: Filter dimention based on last row with data for last column
« Reply #1 on: 15 May 2018 03:31:51 pm »
Hi,

you need dimensional functions. First you get the last member of 2018 which would be something like lastChild([2018]). If the year being a member in this example should be dynamic too you need some regular date function.

tail(set;3) would return the last 3 members of a set. The set could be 2018 and so on.

Just google Cognos mdx functions to get a list.

Regards

Offline qvixote

  • Community Leader
  • *****
  • Join Date: Jul 2013
  • Posts: 108
  • Forum Citizenship: +1/-0
Re: Filter dimention based on last row with data for last column
« Reply #2 on: 15 May 2018 03:43:06 pm »
Thanks for your reply!

YEAR and WEEK are independent dimentions, they are not part of the same hierarchy; so, lastChild([2018]) would give me members thar are not useful in this case.

If I try tail([week];3) it returns ([51],[52],[53]), because those are the last weeks. If I try tail([2018];3) it return a different dimention, because, as I've said, WEEK is not in the same hierarchy than YEAR, they are independent dimentions.

Any other idea?

Thanks again.

Offline Reinhard

  • Community Leader
  • *****
  • Join Date: Nov 2017
  • Posts: 135
  • Forum Citizenship: +1/-0
Re: Filter dimention based on last row with data for last column
« Reply #3 on: 16 May 2018 03:01:45 am »
Ok, got it. If the "last" week of the current year is always the current week, then you could use

cast(_week_of_year(current_date);integer)=caption([week])

That would give you your starting point to go back 3 weeks and so on.


However, if your week does not correlate with the todays date, then you could use lastChild([week]) which would give you the last child of all weeks because even though previous years had 52 years Cognos does continually number them so the newest one is the last one.

btw. [week] is the level week in these examples.

Offline qvixote

  • Community Leader
  • *****
  • Join Date: Jul 2013
  • Posts: 108
  • Forum Citizenship: +1/-0
Re: Filter dimention based on last row with data for last column
« Reply #4 on: 16 May 2018 01:38:46 pm »
It did the trick!

The only change I've made to your suggestion is to change cast data type to varchar(2), because caption() returns string.

Thanks a lot.

 


       
Twittear