topic Aggr dimensions and global selection in QlikView

Aggr dimensions and global selection

Thu, 15 Apr 2010 10:34:30 GMT

Hi,

I have many fact tables, with composite keys intersecting each other in different ways.

I used Link Table design, so I have a common "Keys" table linking each fact.

Each fact table has a data field, so a data selection on one of them results in a selection on "Keys" linktable, and so on each other fact.

My pivot's calculated dimension is

aggr( sum({1<OTHER_KEY_PART={$(Only(OTHER_KEY_PART))}, FACT2_YEAR={$(Only(FACT1_YEAR))}>} FACT2_SALES), MY_KEY_PART)

unfortunately for the selected year there are not key values into FACT1 matching FACT2, but that does not interest me, for I need to calcutate sum over all existing key values for selected year into FACT2.

My pivot's dimension is always '-', and I think it's because selection generates an empty-set for aggr dimension.

So I need to find a way to make aggr dimension selection-insensitive.

Any suggestion?

Thanks a lot!

Aggr dimensions and global selection

d_pranskus — Thu, 15 Apr 2010 11:10:48 GMT

Would you attache sample application with sample data?

Cheers

Darius

Aggr dimensions and global selection

Thu, 15 Apr 2010 11:47:23 GMT

Hi Darius!

Here is a sample...

Thanks!

Aggr dimensions and global selection

d_pranskus — Thu, 15 Apr 2010 12:12:06 GMT

Just a clue. Could it be that a broblem is related to the fact that when you select Year 2009 and DestIst, it leaves possible values in DescComp with no transactions. It seems that AGGR function and {1} designator works a little bit differently in diemnsions and expressions.

Cheers

Darius

Aggr dimensions and global selection

Thu, 22 Apr 2010 09:13:49 GMT

nobody has any other ideas?