Showing missing values and year in chart

[#1]

Hi,

I would like to create a chart  which will display the missing values

ex. Assuming below is my Year table, category table and order table

[YR]:

load * inline

[YR

2004

2005

2006];

[CAT]:

load * inline

[CAT

A

B

C

D];

[ORD]:

LOAD * inline

[CAT,YR,AMT

A,2005,100

A,2006,200

A,2005,100

C,2006,200];

Here is the chart I would like to  create . Show all categories A, B & C . Also show all years as well. Ex. It shoul list B and all the years with 0 as amount.

Any idea ?

Thanks,

AP

[Anonymous]

Aji

Does the attached give you food for thought ?

Best Regards,    Bill

[#3]

Bill,

Thanks for the quick reply!

Is there any way other than creating dummy (0) values? I have few dimensions , so in that scenario I have to loop thru all the dimensions.

Thanks,

AP

[#4]

Hey Aji,

The trick is to unckeck 'Suppress Zero Values' in Presentation tab. As long as there are valid dimensions(in this case Year) even if the expression results in zero or null() still it would be shown in the table. This is what Bill Markham did.

Hope it helps.

Thanks

AJ

[Anonymous]

AJ, AP,

Bill Markham did a little more than that...  He modified data model by  joining all table.  But adding AMT=0 seems unnecessary.  Try to remove script lines "0 as AMT" - the result is the same.

Regards,

Michael

[#6]

Ajay,

I mean without joining the table is it possible ?

--------------------------------Single table w/Outer Join ----------------------

[YR]:

load * inline

[YR

2004

2005

2006];

outer join (YR)

load * inline

[CAT

A

B

C

D];

outer join (YR)

LOAD * inline

[CAT,YR,AMT

A,2005,100

A,2006,200

A,2005,100

C,2006,200];

-------------------------Individual tables --------------------------------

[YR]:

load * inline

[YR

2004

2005

2006];

[CAT]:

load * inline

[CAT

A

B

C

D];

[ORD]:

LOAD * inline

[CAT,YR,AMT

A,2005,100

A,2006,200

A,2005,100

C,2006,200];

[#7]

Thanks Michael!

I have a very complex data structure so I was wondering whether its possible to do with out joining.

Thanks,

Aji

[#8]

I think without Joining its not possible(because there is no way of telling QV that A,B,C and D should all have all three years) . You dont have to join the entire table to Ord. You might be having 20 fields in YR and CAT but just join Year and CAT like in the example.

[Anonymous]

Aji,

It is possible without joining, but it is more complex.  See attached, for example.  You'll not like it...

Regards,

Michael

[#10]

Hi,

My proposal in attached (after several tries...): since the number of years and categories should be small, you could load them in new independant tables without joining to ORD (by naming fields differently, YR2 and CAT2 for instance, or use Qualify/Unqualify). Your pivot chart could then use YR2 and CAT2 as dimensions, and as expression Sum(If(CAT=CAT2 and YR=YR2, AMT))

Hth