topic only expression not working.. in QlikView

only expression not working..

cafcptg2011 — Wed, 11 Feb 2015 15:13:32 GMT

Hi,

I need to to know client that has only SalesDate=StoreDate but hasn't sales in another date, How can I exclude the clients with more then one sales.

my data:

I need to obtain a pivot table with :

Store | Cliente

A | 2

B | 5

my currente expression is : only({<SalesDate={$(=Date(StoreDate))}>} Client) but doesn't work! 😞

my qvw is here http://community.qlik.com/servlet/JiveServlet/downloadBody/7918-102-1-10720/TesteOnly3.qvw

Could someone help me please,

thanks in advance,

CAFC

Re: only expression not working..

Gysbert_Wassenaar — Wed, 11 Feb 2015 18:08:25 GMT

See this document: set_analysis_intra-record.qvw

Re: only expression not working..

cafcptg2011 — Wed, 11 Feb 2015 18:16:13 GMT

hi, thanks for your, reply

as far as I understood, i should use an expression like this:

count(Distinct if(SalesDate = StoreDate, Client))

i am correct??

it just doesn't works.... 😞

sorry...

thanks

Re: only expression not working..

Gysbert_Wassenaar — Wed, 11 Feb 2015 18:23:48 GMT

See attached qvw.

Re: only expression not working..

cafcptg2011 — Wed, 11 Feb 2015 18:29:51 GMT

what i need is to filter only the client that has 1 sales when SalesDate is equal to StoreDate, right?

in this case is de client 2 and 5 only..

thanks very much for help..

Re: only expression not working..

Oleg_Troyansky — Wed, 11 Feb 2015 18:49:25 GMT

Try something like this:

count( {<Client={"=count(If(SalesDate=StoreDate, SalesDate))=1"}>} Distinct Client)

1. You want to filter clients, hence your Set Analysis modifier should be Client.

2. You want to filter those clients that had only one Sales record when SalesDate=Store Date, hence the advanced search expression within the modifier.

cheers,

Oleg Troyansky

Come and learn Set Analysis and Advanced Aggregation with me at www.masterssummit.com - take your QlikView skills to the next level!

Re: only expression not working..

cafcptg2011 — Thu, 12 Feb 2015 10:03:59 GMT

hi, thanks for your reply..

I have tested your expression...but unfortunately it doesn't work.. 😞

it still shows the client 1.... but it should only present client 2 and 5....not client 1.. because, only the client 2 and 5 has the sales in same date (ie, SalesDate = StoreDate) one time... the client 1 has sales when SalesDate = StoreDate but has others sales in another days....

here is my qvw..

http://community.qlik.com/servlet/JiveServlet/downloadBody/7919-102-1-10724/only4.qvw

thanks in advance,

Re: only expression not working..

maxgro — Thu, 12 Feb 2015 18:36:54 GMT

if(SalesDate=StoreDate, 1)

Re: only expression not working..

Oleg_Troyansky — Thu, 12 Feb 2015 18:40:21 GMT

I maybe misunderstand your logic, but the expression works the way you asked for...

It only counts those clients that only had a single sale where SalesDate=StoreDate. Your Client 1 has 3 Sales, but only 1 of them happened on the StoreDate, therefore client 1 should be included in the chart.

If you wanted something different, please rephrase your requirement.

Re: only expression not working..

Gysbert_Wassenaar — Thu, 12 Feb 2015 18:51:50 GMT

Take 2. See attached qvw.

Re: only expression not working..

cafcptg2011 — Fri, 13 Feb 2015 09:42:03 GMT

thank you very much... your approach is acceptable...i would prefer not alter the load script....but your solution works ..that it is the most important...again thank you very much for help...

Re: only expression not working..

cafcptg2011 — Fri, 13 Feb 2015 09:43:00 GMT

sorry...but your solution doesn't work in pivot table..

thanks anyway

Re: only expression not working..

cafcptg2011 — Fri, 13 Feb 2015 09:45:38 GMT

I'm sorry.. but can't agree with you...I have explained correctly what i needed...

but anyway thank you very much for your effort.

regards,