BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
rufmau68
Obsidian | Level 7

i have a simple hierarchy (continent, state...) and 2 measures (revenues, items number)

i needs to see (in a cross table, or other objects) just the items number if the user drill to continent, but i need to see the revenue measure if the user drill down to state category

1 ACCEPTED SOLUTION

Accepted Solutions
FredrikE
Rhodochrosite | Level 12

I tried to do "Count distinct" on level2.

When on level 1 I got a value greater than 1 (Level 2 need to have more than one member thou').

*When on level2 I got the value 1 for each row.

I then created a calculated aggregated meassure that did an "If-then-else" and picked measure 1 or 2 depending on the "count distinct" variable.

 

Maybe you can do some more calculations to be sure that you are on the correct level?

 

Why I suggested the two list tables is that I don't really like showing different measures in the same column/table, I think it will confuse the users. I have done a lot of this using OLAP cubes and MDX, but it always got complicated and hard for my (especially new) users to understand.

 

I would try to separate it and create filters/interactions to get the same outcome 🙂

//Fredrik

View solution in original post

4 REPLIES 4
FredrikE
Rhodochrosite | Level 12

Hi!

It's not working out of the box!

You can somehow determine which level you are at by counting distinct level members, but it failes if the sublevel only contains 1 item, i.e 1-1. And from that create a calculated aggregated meassure that uses either item or revenue depending on the flag. 

 

Why not use two list tables one for items number and one for revenue and link them together?

 

//Fredrik

rufmau68
Obsidian | Level 7

thanks FredriK,

the workaround with 2 table list object , i'm sure that it work, with interation between the 2 object

 

but i'm interested to understand if there is a way to know the drill level where the user lands....

your first suggestions its interesting but i'm not sure that i understand it, what U compare ?

eg.: hierarchy categories ---> lev1, lev2 and lev3

distinct(lev1) vs. .... ?

 

 

 

 

FredrikE
Rhodochrosite | Level 12

I tried to do "Count distinct" on level2.

When on level 1 I got a value greater than 1 (Level 2 need to have more than one member thou').

*When on level2 I got the value 1 for each row.

I then created a calculated aggregated meassure that did an "If-then-else" and picked measure 1 or 2 depending on the "count distinct" variable.

 

Maybe you can do some more calculations to be sure that you are on the correct level?

 

Why I suggested the two list tables is that I don't really like showing different measures in the same column/table, I think it will confuse the users. I have done a lot of this using OLAP cubes and MDX, but it always got complicated and hard for my (especially new) users to understand.

 

I would try to separate it and create filters/interactions to get the same outcome 🙂

//Fredrik

rufmau68
Obsidian | Level 7

I tried,

great! it works

 

in that way U change column contents, but not column header... so U need to insert any dynamic disclaimer to suggest the user about the really column content (drill by drill...), but its a good way!!!!

 

now in the week end i try to have a dynamic column number in the drill...

 

and in this case i try using measure not stored in column of a source table , but stored in rows of a sourece table

 

thanks a lot

bye

Mauro

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 1124 views
  • 1 like
  • 2 in conversation