Exploring, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

Different measure for each drill down level in VA

Accepted Solution Solved
Reply
Contributor
Posts: 21
Accepted Solution

Different measure for each drill down level in VA

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


Accepted Solutions
Solution
‎05-04-2018 03:57 AM
PROC Star
Posts: 401

Re: Different measure for each drill down level in VA

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 Smiley Happy

//Fredrik

View solution in original post


All Replies
PROC Star
Posts: 401

Re: Different measure for each drill down level in VA

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

Contributor
Posts: 21

Re: Different measure for each drill down level in VA

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. .... ?

 

 

 

 

Solution
‎05-04-2018 03:57 AM
PROC Star
Posts: 401

Re: Different measure for each drill down level in VA

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 Smiley Happy

//Fredrik

Contributor
Posts: 21

Re: Different measure for each drill down level in VA

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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