topic Re: when adding dimensions key attribute gets "lost"? in QlikView

when adding dimensions key attribute gets "lost"?

Anonymous — Fri, 06 Mar 2015 22:50:00 GMT

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

Re: when adding dimensions key attribute gets "lost"?

rwunderlich — Sat, 07 Mar 2015 00:09:39 GMT

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

Re: when adding dimensions key attribute gets "lost"?

Anonymous — Sun, 08 Mar 2015 14:22:21 GMT

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

Re: when adding dimensions key attribute gets "lost"?

petter — Sun, 08 Mar 2015 15:37:24 GMT

Could you please upload your sample data or application?

Re: when adding dimensions key attribute gets "lost"?

Anonymous — Sun, 08 Mar 2015 15:48:54 GMT

yes

it is attached

Re: when adding dimensions key attribute gets "lost"?

awhitfield — Mon, 09 Mar 2015 15:01:29 GMT

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

Re: when adding dimensions key attribute gets "lost"?

Anonymous — Mon, 09 Mar 2015 15:30:33 GMT

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

Re: when adding dimensions key attribute gets "lost"?

awhitfield — Mon, 09 Mar 2015 15:42:54 GMT

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

Re: when adding dimensions key attribute gets "lost"?

Anonymous — Mon, 09 Mar 2015 16:00:44 GMT

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

Re: when adding dimensions key attribute gets "lost"?

awhitfield — Mon, 09 Mar 2015 16:04:17 GMT

Look in the example on my last reply please

Re: when adding dimensions key attribute gets "lost"?

Anonymous — Mon, 09 Mar 2015 16:12:34 GMT

im a dummy.. missed it

sorry will look into it.

thanks

Re: when adding dimensions key attribute gets "lost"?

Anonymous — Mon, 09 Mar 2015 17:01:03 GMT

ah..

so theres no way to do.. where i can split the style to be a dimension up top?

the format the users are looking for is.. styles up top.. colors/customers on the side..

like the setup i have where i get this issue.

thanks

Re: when adding dimensions key attribute gets "lost"?

rwunderlich — Mon, 09 Mar 2015 22:39:03 GMT

I think your solution is to use this for the background color expression:

if(sum(TOTAL<style, color>active)>0,blue(),red())

See attached.

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: when adding dimensions key attribute gets "lost"?

Anonymous — Mon, 09 Mar 2015 23:12:55 GMT

perfect...

i was going down this route.. but i couldnt figure out where the active # went.. by the time i got to the expression..

total --- so simple.. yet so hard..

thank you sir..