topic Dynamic caption bar in a table with a multiple drilldown? in QlikView

Dynamic caption bar in a table with a multiple drilldown?

datanibbler — Thu, 18 Apr 2013 14:32:02 GMT

Hi,

for a planned new Dashboard, I have to create a drilldown group. In principle, I have already made that, I just have to enlarge it to encompass more levels. I have it displayed in a straight table, that way the "dimensions" are the rows and my calculated figures are in the columns.

I want to make the caption bar dynamic now using a formula. I have already made that - partly. Currently, there is a formula:

'Turnover figures per ' & Getcurrentfield (Mygroup)

Now I want to build on that and I want the thing to say

'Turnover figures per ' & Getcurrentfield(Mygroup) & ' within ' & [the_next_level_up]

Can anybody tell me how I can do that?

Thanks a lot!

Best regards,

DataNibbler

Re: Dynamic caption bar in a table with a multiple drilldown?

Gysbert_Wassenaar — Thu, 18 Apr 2013 17:10:38 GMT

You'll have to use some expression that calculates [the_next_level_up]. Something like:

if( Getcurrentfield (Mygroup) = 'LevelX-1', 'LevelX',

if( Getcurrentfield (Mygroup) = 'LevelX-2', 'LevelX-1',

if( Getcurrentfield (Mygroup) = 'LevelX-3', 'LevelX-2')))

Re: Dynamic caption bar in a table with a multiple drilldown?

datanibbler — Fri, 19 Apr 2013 12:22:18 GMT

Hi Gysbert,

thanks for the help! I was hoping there would be an easier way by using something like a "Getcurrentparentfield" function. But I guess your solution will work out. It will just be a bit time-consuming because I will have a lot of possibilities and that way I'll have to type up every single one.

Can I build a CASE construction in QlikView?

Best regards,

DataNibbler

P.S.: The Getcurrentfield function won't help me here, but I have yet to find the right one: >Getcurrentfield(Mygroup)< will return the level I am currently on, but not the specific field:

=> Scenario: I have four units on the "big box" level with each one having four possible "small box" units under it - so it will not suffice to know that I am on the "small box" level, but I have to identify which of the four sets of possible "small box" values I have to determine which of the "big box" units I am in.

Is there a function I can use to return the currently displayed values (the values there are on the current level)?

P.P.S.: Oh yes, it is not only possible - it is much easier than I thought. The GETCURRENTSELECTIONS function with no specific parameters returns exactly what I want, namely the exact level (and field) within the drilldown group I just came down from.

I just have to find a way to insert some line-feeds (so that the text is properly aligned). I haven't yet found the right command for line-feeds. Can you give me a hint here?

Re: Dynamic caption bar in a table with a multiple drilldown?

datanibbler — Fri, 19 Apr 2013 14:16:44 GMT

Hi,

okay, I found it out now: The chr(10) function is the SQL statement for a line_feed.

This is solved then.

Best regards,

DataNibbler

Re: Dynamic caption bar in a table with a multiple drilldown?

datanibbler — Fri, 19 Apr 2013 16:16:16 GMT

Hi,

there's another detail: It works and looks nice in a big textbox where I can have the whole thing displayed in several lines.

However, acc. to the presentation I have as a guideline for the look, there is supposed to be one cell in the table - the leftmost uppermost cell, where I would usually have the name of my drilldown group, where the >item_on_the_level_I_just_came_from< is displayed.

I am already almost there: I cannot make the name of the group into a formula or so, so I just added a textbox which I moved over that cell. In the textbox, I again display the result of the function >Getcurrentselection()<; The only problem I have now is that that function returns all the upper levels - but I want only one, the next_one_up.

Can anybody advise me how to modify that function to get only the next level?

Thanks a lot!

Best regards,

DataNibbler