How to get the higher level field name from a drill down group

[#1]

Assume here we have a group, named as 'New Group'

The fields in this group are: Year-->Quarter-->Month

if using below function, we will be easy to get the current field of this group.

     =GetCurrentField([New Group])

But, how to get the higher level field name from this group?

E.g: right now I select [Month], so this function return 'Month'. I want to find a way to return 'Quarter'. And alos, if choose [Quarter], then return 'Year'.

Above screen-shot is what I want to do. Here I have a group: Y-->Q-->M

When I drill down to month, I still need the value of "summary by quarter", here it should be 3.

so, the fill rate will be =column(1)/column(2)=1/3

When I scroll-up to quarter, I wish the value can be auto changed to "summary by year", means 12.

so, the fill rate will be =colum(1)/column(2)=3/12

I know this is a little stupid question, actually it can be easily handled by "conditional" property of expression. And also I can use 'if ... then' to resolve.

I ask this question is not asking for a workaround, I just want to learn "how many way can I used to calculate this KPI & are there any useful function of group", and which way is the best practice.

[jagan]

Hi,

There is no such method in Qlikview to get higher level field name from Drill Down group, you need to handle it by using IF statement.  The best way is store 3/12 in variable by using expression, and then use this variable value in the expressions, so the calculation logic would be at a single place and then the value is reused wherever you required.

Create a variable

vMonths = If(GetCurrentField([New Group]) = 'Month', 3, GetCurrentField([New Group]) = 'Quarter', 12, 1)

Now in your you just use vMonths variable.

Hope this helps you.

Regards,

Jagan.

[#3 · Accepted solution]

Hi Jagan,

Thanks for your reply.

As I said in previous, I know I can use 'if ... then' to resolve. But this solution is not well enough if I am developing an ad-hoc report.

vMonths = If(GetCurrentField([New Group]) = 'Month', 3, GetCurrentField([New Group]) = 'Quarter', 12, 1)

Since the 3,12,1 will be replaced as an expression, assume the dimension can be switched/added/removed, I believe this will cost more effort to finish this expression. Eg: 3 should be replaced as =sum({1<Q={'$(=GetFieldSelections(Q))'}>} TOTAL S)

Anyway, thx a lot. This is not the first time you response my question, you are very warm-heart.

I found a solution for this requirement:

=sum({$<$(=getCurrentField([New Group]))=>} TOTAL S)

The syntax is :

     =Sum( $ <[Field Name]=> [Expression] )

Since there has nothing after =, means "ignore the current selection". So, if ignore the current selection of group, the result will be 1 layer higher than current selection.

.