Skip to Content

***** NOTE In 03/01/2016 *********

If your requirement is to get the list of values of a dimension [dim] , use ReportFilter([dim]). This will give you the LOV of [dim] , separeted by “;”.

If you need the list unfiltered use NoFilter(ReportFilter([dim])).

Regards,

Rogerio

Hi,

Sometimes we face a requirement of concatenating values from an object., like below screen :

/wp-content/uploads/2014/11/concatenate1_595904.png

This can be achieved with 3 variables :

[conc] , a dim variable

[conc] = ([State]+”,”+Previous(Self));

[final] a measure variable

[final] =Last ([conc] ForEach ([State])) In Report

[No_ending_comma] a measure variable

[No_Ending_Comma] = Left([Final];Length([Final])-1)

How does it work :

The [conc] variable, will concatenate the value of [State] and the previous value of [conc], since the Previous value of ‘California’ doesn´t exist, it returns null and the the first row of [conc] is justa California. Refer to the above image, column [conc] , to see the values of [conc] as [State] is shown.

The  second variable [final] is where the ‘trick’ works :

It takes the last values of [conc] in the report calculated in the context of [State]. I use the ForEach operator so I can get the last value independent of the context used.

This example shows the values concatenated in descending order. To get it in ascending order, use [conc_asc] = (Previous(Self)+”,”+[State])

[final_asc] = =Last ([conc] ForEach ([State])) In Report

[No_ending_comma_asc  =  Rigth([final];Length([final]-1)

And what about the following cenario :

The requirement is to concatenate the stores name for each state

Here, I queried e-fashion for [State] and [Store Name]

/wp-content/uploads/2014/11/concatenate2_595905.png

I altered [conc] to :

=(Previous(Self;([State]))  ForEach([State];[Store name]) +”,”+([Store name] ForEach([State];[Store name])))

and

[final] to :

=Last ([conc]ForEach([State];[Store name])) In ([State])

Cheers,

Rogerio

the stores name for each state

To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

  1. Andrea Molina

    Hi Rogerio Plank . Thanks for your post. It helped a lot.

    Just one question. I need to show just the top 3 data and I’m using ranking and it is not working. I get the 3 mixed. Any idea why?

    Thanks,

    Andréa

    (0) 
    1. Rogerio Plank Post author

      Hi Andrea,

      thanks for your comment.

      Can you give me an example in a Excel file, so I can try working it out ?

      Regards,

      Rogerio

      (0) 

Leave a Reply