Count first two columns

[Former Member]

Hello,

I have a count requirement that i havent been able to solve by searching sdn. Maybe someone can help.

I have a query built on a multiprovider that points to two basic cubes. The basic cubes do not contain a count key figure. The first two columns of my query are characteristics. My requirement is to count the occurences of the combination of the first two column (characteristics). I am looking for a way to do this in the query so i do not have to reload the cubes as they have large volumes of data. All the suggestions i have been able to find on sdn seem to be only for counting a single characteristic. Is there a way to count the combination of two?

Regards,

TMS

[Former Member · Accepted solution]

Does anyone have any suggestions on this one? I am still working on it and would appreciate any input.

TMS

[Former Member · Accepted solution]

Hi Create a count using the formula or calculated ket figure for the count unique id like client id for your infocube.

so you will have a count for each row now. place this count object in your column.

now select the two char or the group whatever you want a count in your row.

select query properties and select the display tab and make sure the option - hide repeated key values is selected (normally by default it is selected). Then select each of the char in rows and in free char and set the option under display tab result rows - always suppress.

Execute the query now. you will get the list of char and their count. sort the count by descending to get the most duplicate entries by their highest number on top in the count coulmn.

you can also ignore the rows which donot have a duplicate i.e count more than 1 by creating a new condintion on the count key figure > 1 and restricting the condition by only the group of char you have given in the column. so that you can also do a drill down by free char in your query and it will only show the duplicates group.

Hope it helps.

regards,

Siva