Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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