when adding dimensions key attribute gets "lost"?

[Anonymous]

hi all

so this is a little convoluted but bear with me.

i have 2 tables.

1 table contains

style

color

active  (0/1 flag)

2nd table contacts

style

color

customer

sales

so i do a pivot table.. where the natural link between the 2 tables are style/color

heres where it gets wonky.

i guess since active is on style/color    and not style/color/customer..

when i do a pivot.. and add customer as a dimension..

then do an expression to add total sales

it loses active = yes/no  for a style color if the customer never purchased the style.

so my issue is.. the style/color is active.. but i need to know its active even if no one bought the style (within the expressions)

so sales is zero.. but since customer never bought it.. it doesnt know about it.. or loses it within the dimension of 2(customer)...

how do i tell it to check dimension 1 for active/inactive?

thanks

[rwunderlich]

On the Dimension Tab, check "Show all values" for each Dimension and uncheck "suppress if null". On the Presentation tab, uncheck "suppress zero values".

-Rob

http://masterssummit.com

http://robwunderlich.com

[Anonymous]

sorry i think i phrased the question wrong..

i did do the suppression uncheck stuff..

but the question is this.. in the expression.. i want it to check if active or not active..   if its active. make color blue..

if not active.. make it red..

this works fine.. if customer purchased it..

but if no customer purchased it.. it reads it as nonactive.. even though for the style/color table has it as active..

i think this is due to the cust. dimernsion.. so how do i respect the active/inactive flag.. even with the extra dimension.. that seems to cause the active/inactive to be lost or clouded.

thanks

[petter]

Could you please upload your sample data or application?

[Anonymous]

yes

it is attached

[awhitfield]

If you try clicking on one the red cells, you can see that the pivot creates a matrix of possible combination for which there are no actual records. e.g there's no such product as Style mp434 in Black, but it appears in the pivot table.

Andy

[Anonymous]

hi andrew..

yes u are correct there is no style mp434/black.. so being red.. is correct..

but the issue becomes this..

style MP234/BLACK is active.. but since..  nord purchased it.. but saks didnt.. under that customer.. it is red. (inactive)

but what i need to display is blue with zero sales in it..

i need to flag the style/color as active even if no sales were made in it.

thats where im stucking..  trying to figure out the if statement to force it to go the dimesion 1 where the active/inactive flag lives...  or some other way of doing this.

thanks

[awhitfield]

For it to work you need the Sum(sales) expression to be in a single column.

[Anonymous]

apologies..

what do u mean by expression in single column..  can u show me an example?

the users want need to see the customer and how much each customer purchased..  so i cannot remove customer

[awhitfield]

Look in the example on my last reply please