Model for hierarchy

[prathipsrinivas]

Hi All,

I have a table with hierarchy , based upon the level of hierarchy, values should be taken.

My Table looks something like this:-

Hierarchy_Code , Nation,  State,  District,     Actual

N,                                   IND,                  ,                   , 45

S,                                   IND,            UP,                   , 50

S,                                   IND,           MP,                     , 65

D,                                     IND,           MP,        XXX   , 78

D,                                     IND,           MP,        YYY   , 65

D,                                     IND,           UP,        ZZZ   , 57

D,                                     IND,           UP,        AAA   , 43

Based on hierarchy code, we are taking actuals and show it in the Dashboard.

I am stuck at how should we able to do that.

I am using set analysis and if statement to show at certain level like below:-

if(GetSelectedCount(State)>0 , Num(sum({<Hierarchy_Code ={'S'}>}Actual),'#,##0.#'),
if(GetSelectedCount(District)>0 , Num(sum({<Hierarchy_Code ={'D'}>}Actual),'#,##0.#'),
 Num(sum({<Hierarchy_Code ={'N'}>}Actual),'#,##0.#')))

it works fine till I make selection at one field without selecting other.

When I select State and then if I make selection on District, it is showing '0'.

Could you please help how to overcome either in UI or how to create a model ?

Any help/recommendations  are highly appreciated.

Thanks in advance,

Prathip

[chrismarlow]

Hi,

I am not convinced that your users are not going to find a way of breaking this through the UI, but as you district are subsections of states, is re-ordering the function you have an option;

if(GetSelectedCount(District)>0 , Num(sum({<Hierarchy_Code ={'D'}>}Actual),'#,##0.#'),
if(GetSelectedCount(State)>0 , Num(sum({<Hierarchy_Code ={'S'}>}Actual),'#,##0.#'),
Num(sum({<Hierarchy_Code ={'N'}>}Actual),'#,##0.#')))

Cheers,

Chris.

[prathipsrinivas]

Thanks Chris for your reply.

I understand that this is not the optimal way, is there any way that we could do in model itself ?

Thanks,

Prathip

[Brett_Bleess]

Have a look at the following Design Blog post, it might give you some further ideas on things, hopefully:

https://community.qlik.com/t5/Qlik-Design-Blog/Unbalanced-n-level-hierarchies/ba-p/1474325

Regards,
Brett