topic Re: sales disregard a selection in QlikView

sales disregard a selection

dtbit123 — Fri, 03 Jan 2014 00:30:33 GMT

hi all,

very simple,

one fact table of sales with fields item id, customer id, and quantity

two tables of items and customers. linked to the sales table.

and the items table has some flag of special items.

I want to show the sales in a chart of all items that flagged.

but if I select a customer the chart should show all items that flagged even if the this customer I select

didn't buy some items that flagged.

please help

thanks,

Re: sales disregard a selection

Peter_Cammaert — Fri, 03 Jan 2014 00:58:48 GMT

Add artificial "zero" records to your sales table, i.e. records for every customer and every is_pl=1 product with a quantity set to 0.

Good luck,

Peter

Re: sales disregard a selection

swuehl — Fri, 03 Jan 2014 01:12:17 GMT

Maybe like this chart only approach?

Re: sales disregard a selection

dtbit123 — Fri, 03 Jan 2014 10:02:48 GMT

thanks this looks good,

but I don't understand , if select custname isn't should show me the sales of all customers anyway ?

because the custname= in the set analysis in the expression.

Re: sales disregard a selection

brijesh1991 — Fri, 03 Jan 2014 10:22:48 GMT

just nullify your customername in set expression

Re: sales disregard a selection

Clever_Anjos — Fri, 03 Jan 2014 10:41:28 GMT

Use sum({$<custname=>}quant) as expression and tick 'supress zero values'

Re: sales disregard a selection

Clever_Anjos — Fri, 03 Jan 2014 10:45:23 GMT

I think using =aggr(Only({$<custname=>} partname),partname) would simpler than your dimension too

Re: sales disregard a selection

dtbit123 — Fri, 03 Jan 2014 12:37:19 GMT

In the expression if you use custname= you will get the sales for all Customers and its not the meaning... I need to see all the items even if the custname I select didn't Buy it Thanks Dt

Re: Re: sales disregard a selection

Clever_Anjos — Fri, 03 Jan 2014 12:54:59 GMT

Hi Doron, in this case, you wanna see "0" for the product, right?

PFA and check if fits your needs

Re: sales disregard a selection

swuehl — Fri, 03 Jan 2014 13:02:14 GMT

but I don't understand , if select custname isn't should show me the sales of all customers anyway ?

because the custname= in the set analysis in the expression.

I can't see custname= in your original expression. Or are you talking about the count in the calculated dimension?

I don't think this will work like you want. And using my approach, I think you don't need the calculated dimension, a simple partname as dimension field should be enough.

Re: sales disregard a selection

dtbit123 — Fri, 03 Jan 2014 14:33:42 GMT

hi Clever,

thanks for the answer.

but I don't understand why does its show all the sales for each part even when I select a custname

because the custname= means disregard of custname selection?

Re: sales disregard a selection

dtbit123 — Fri, 03 Jan 2014 14:38:10 GMT

in your solution,

I don't see why its not show the whole sales even when I select custname. ?

the set analysis in the expression means there is no select in the field custname.

Re: sales disregard a selection

swuehl — Fri, 03 Jan 2014 14:46:58 GMT

Sorry, I don't get what you want.

In my solution, when you select customer x, you'll see

part	sum(quant)
	5
a	2
c	3
e	0

You'll see a row for part e like requested, and the sum(quant) is zero, because the customer x doesn't have any records for this part. I assumed this is what you want to see. If not, please clarify what the expected result is when selecting customer x.

Re: Re: sales disregard a selection

dtbit123 — Fri, 03 Jan 2014 14:55:47 GMT

see the attach.

Re: Re: sales disregard a selection

dtbit123 — Fri, 03 Jan 2014 15:02:12 GMT

I cant, since I will show sales for all customers even if I select a custname...

Re: sales disregard a selection

Clever_Anjos — Fri, 03 Jan 2014 15:08:42 GMT

Yes {<custname=>} instructs QV to disregard any selection into custname field

Re: sales disregard a selection

swuehl — Fri, 03 Jan 2014 15:17:49 GMT

Right, the set expression will ignore your selection.

But do you still have an issue? Sorry, I don't really get your latest requests.

As you see, I am using two expressions, one without set analysis to show the values for your selection, and a hidden expression with set analysis to extend the rows to the ones without sales (this one ignores selection in customer).

Re: Re: sales disregard a selection

dtbit123 — Fri, 03 Jan 2014 16:01:03 GMT

sorry its my miss understand...

but unfortunately its doesn't work in production...

Re: Re: sales disregard a selection

dtbit123 — Sat, 04 Jan 2014 22:35:59 GMT

hi,

ok, now I understood it.

you add a hidden expression, well this is not good because I need it in a pivot table,

and there I cant hide expression.

the real problem here is the flag "is_pl", when is_pl=1 its cuts the rows

for those items the customer bought and then we loose item "e"

thanks,