How to include a constant value in pivot table

[#1]

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

[#2]

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)

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?

>

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

[swuehl · Accepted solution]

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

[#4]

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

All the best

Frank

[#5]

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

[swuehl]

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

[swuehl]

Maybe like attached, using advanced aggregation.

[#8]

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

[swuehl]

if(column(1),

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

)

[#10]

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

Best wishes

Frank