topic Re: How to include a constant value in pivot table in QlikView

How to include a constant value in pivot table

Thu, 28 Jan 2016 14:08:43 GMT

Hello,

I have a two-dimensional pivot table with a regional dimension (Key: SKEY_R, Labels: TEXT_R) and a time dimension (KEY: SKEY_Z). The fact F1 is summed up.

As a second fact I'd like to have the value of the top of the regional structure (SKEY_R = BRD) relating the respective month. That means that the zeroes in the picture below should have the values 174810 and 172408 respectively.

Any ideas?

Many thanks in advance

Frank Backes

Re: How to include a constant value in pivot table

swuehl — Thu, 28 Jan 2016 20:17:15 GMT

Would be helpful if you state the expressions you are using, too.

You can look into the TOTAL qualifier with field lists, so if your only expression is Sum(F1), try something like

=If(SKEY_R='BRD', Sum(TOTAL<SKEY_Z> F1), Sum(F1) )

Re: How to include a constant value in pivot table

Fri, 29 Jan 2016 15:36:37 GMT

Many thanks for the quick answer!

I have attached another screenshot to make my point more clear:

Here we can see the numbers for two regions VTB 1000000 and VTB 200000 for two time keys 201523411 and 201523412.

There are actually two more regions, which are not selected in the respective filter. The top of the regional hierarchy is 'BRD', which consists of the four regions. The values (Field F1, Label 'Absatz') for the regions are summed up from their children: areas, subterritories and segments.

I the column 'Absatz BRD' I always want to see the value for 'BRD' dependent on the respective time key. These would be 174.810 for 201523411 and 172.408 for 201523412.

Expression for 'Absatz': sum(F1)

Expression for 'Absatz BRD': sum({<TEXT_R={'BRD'}>} F1)

The latter does not work. I guess that's because 'BRD' is not the actual value of TEXT_R in the actual line.

Re: How to include a constant value in pivot table

swuehl — Fri, 29 Jan 2016 17:41:45 GMT

Try something like

Sum( Aggr( sum({<TEXT_R= >} TOTAL<SKEY_Z> F1), TEXT_R, SKEY_Z))

Re: How to include a constant value in pivot table

Fri, 29 Jan 2016 20:00:03 GMT

Many thanks again! 🙂

Unfortunately that expression doesn't deliver the right results:

Isn't it possible to calculate the sum of F1 where one dimension member is fixed (TEXT_R = 'BRD') and the others (time) are changing according to their position in the grid? Isn't that a common requirement? E.g. to calculate percentages relating to a special member of a dimension or to calculate market shares?

Since I'm totally new to QlikView I think I first have to learn more about expressions. I'm not able to fully interpret your suggestions yet. 😉

Re: How to include a constant value in pivot table

swuehl — Fri, 29 Jan 2016 21:24:14 GMT

Could you post a small sample QVW? Or an Excel with a sample table of your field values?

It seems I don't really understand your setting. Is 'BRD' a value in field TEXT_R?

Re: How to include a constant value in pivot table

Tue, 02 Feb 2016 10:28:25 GMT

I've made up a little demo QVW with a stripped down hierarchy. There are some diamond shapes in the hierarchy (please see pic).

The expression you suggested ("Sum( Aggr( sum({<TEXT_R= >} TOTAL<SKEY_Z> F1), TEXT_R, SKEY_Z))") leads to the right values except for the totals. It also assumes that the Column "Units L1-0" refers to the grand total of the hierarchy. What I'm ultimately looking for is a formula to calculate the sum of units for an arbitrary member of the dimension. That means, instead of L1-0 it might as well be L2-2 or the parent of the respective member. It must not necessarily be the top member of the hierarchy.

I hope I could make my point more clear now. It's not that easy to describe. 🙂

Many thanks in advance

Frank

Re: How to include a constant value in pivot table

swuehl — Tue, 02 Feb 2016 11:07:17 GMT

Maybe something like attached?

Re: How to include a constant value in pivot table

Tue, 02 Feb 2016 12:53:52 GMT

Thank you very much! Alternate states seem the way to go. Your solution works quite well!

I still have two issues:

1) The expression only deliveres values when the selected benchmark member is not filtered out by the selection of a Region type. If I select L1-0 as benchmark and set a filter to see only Level 2 members, the results of the expression are 0.

2) How can the benchmark be set without using a listbox? Can it also be set dependent on the current member in the pivot table (context sensitive)?

Many thanks again!

Frank

Re: How to include a constant value in pivot table

swuehl — Tue, 02 Feb 2016 13:28:31 GMT

The first requirement can be solved by clearing the appropriate field selections in the set expression:

if(column(1),Sum ({<TEXT_R= {'$(=Maxstring({AS} TEXT_R))'}, NODE_TYPE_R= >} TOTAL<SKEY_Z> Aggr( sum({<TEXT_R= {'$(=Maxstring({AS} TEXT_R))'}, NODE_TYPE_R= >} TOTAL<SKEY_Z> F1), TEXT_R, SKEY_Z)))

Regarding the second requirement, I think you just need to create an expression that returns the correct value, i.e.

instead

=Maxstring({AS} TEXT_R)

which will return a selection from the alternate state listbox, we would need something else. What would be the rule to determine the single TEXT_R value you want to use as benchmark, for any given set of possible dimension values?

Besides this, I would recommend that you are looking in the HIERARCHY LOAD in the reference manual and

Hierarchies‌

This may allow you to potentially solve your requirements in the data model.

Re: How to include a constant value in pivot table

Wed, 03 Feb 2016 12:33:41 GMT

Many thanks for your very valuable input!

The new expression works perfectly!

Regarding the second requirement, I think you just need to create an expression that returns the correct value, i.e.

instead

=Maxstring({AS} TEXT_R)

I'm about to adapt my DWH data model to QlikView. This model is quite generic and data driven. Many relationships between dimension members are stored in tables. Comparisons or calculation of market shares are driven by a table that holds the key of the member and the key of the respective member for the comparison. That model is quite versatile and significantly eases customizing. What I'm looking for is an expression that considers the current member of the data cell and finds the member to compare with from a table.

Thank you very much for the link to the excellent article covering hierarchies. I've already tried hierarchies in QV before and the table T_PC_RG in my example actually is an ancestor list. There are several types of hierarchies in my DWH model too and they correspond quite well with the types described in the article.

I really think I have to get a much deeper knowledge of sets, expressions, AGGR, etc.. Can you recommend some more articles like the one on hierarchies or any other sources or literature? The books I read so far really don't go deep into the details.

Many thanks again and best wishes

Frank

Re: How to include a constant value in pivot table

swuehl — Thu, 04 Feb 2016 00:48:36 GMT

I can recommend all articles and tech docs of the Qlik design blog, especially the ones written by Henric:

Qlik Design Blog

The other authors are of course also worth reading.

Don't forget to read the in-depth tech docs referenced in the design blog posts.

Marcus has collected some books about Qlik:

Books and literature

I can highly recommend QV11 for developers, Mastering QV and QV your business.

There are also now a lot of QV related blogs:

http://www.askqv.com/blogs/

Regards,

Stefan

Re: How to include a constant value in pivot table

Thu, 04 Feb 2016 08:23:09 GMT

Thank you so much, Stefan! Can't wait to read the articles you recommended!

All the best

Frank

Re: How to include a constant value in pivot table

Thu, 04 Feb 2016 19:50:21 GMT

There is only one small piece of the puzzle missing. 😉

I have added the field P_SKEY_R to the dimension table T_DIM_REG. That field holds the SKEY_R of the member to compare with. I have also added a column called "Units Parent" to the pivot table. This column should display the units of the P_SKEY_R of the current SKEY_R or TEXT_R.

The expression I use doesn't work:

if(column(1),Sum ({<SKEY_R={P_SKEY_R}, NODE_TYPE_R= >} TOTAL<SKEY_Z> Aggr( sum({<SKEY_R={P_SKEY_R}, NODE_TYPE_R= >} TOTAL<SKEY_Z> F1), SKEY_R, SKEY_Z)))

What would be the right term instead of "SKEY_R={P_SKEY_R}"? The goal is to get the member to compare with dynamically from a table.

Many thanks in advance

Frank

Re: How to include a constant value in pivot table

swuehl — Mon, 08 Feb 2016 16:37:00 GMT

You can't use set analysis for this easily, since set analysis is evaluated once per chart and not per dimension line.

Re: How to include a constant value in pivot table

swuehl — Mon, 08 Feb 2016 17:02:36 GMT

Maybe like attached, using advanced aggregation.

Re: How to include a constant value in pivot table

Tue, 09 Feb 2016 11:46:14 GMT

Very nice! 🙂 Now the context sensitive aggregation works! Thank you!

It seems that only one month can be selected. As soon as 201511 and 201512 or no month is selected, the values are only shown for 201511.

Best regards

Frank

Re: How to include a constant value in pivot table

swuehl — Tue, 09 Feb 2016 14:25:20 GMT

if(column(1),

Sum({<TEXT_R=>} Aggr( Sum({<TEXT_R=>} TOTAL <P_SKEY_R,SKEY_Z> F1), P_SKEY_R , TEXT_R, SKEY_Z ))

)

Re: How to include a constant value in pivot table

Wed, 10 Feb 2016 08:57:26 GMT

Perfect! Many thanks, Stefan! You really saved my day. 🙂

Best wishes

Frank