topic Re: Model for hierarchy in QlikView

Model for hierarchy

prathipsrinivas — Sat, 16 Nov 2024 18:05:09 GMT

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

Re: Model for hierarchy

chrismarlow — Mon, 21 Sep 2020 18:54:51 GMT

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.

Re: Model for hierarchy

prathipsrinivas — Tue, 22 Sep 2020 15:03:15 GMT

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

Re: Model for hierarchy

Brett_Bleess — Wed, 14 Oct 2020 17:28:25 GMT

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