only expression not working..

[cafcptg2011]

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

[Gysbert_Wassenaar]

See this document: set_analysis_intra-record.qvw

[cafcptg2011]

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

[Gysbert_Wassenaar]

See attached qvw.

[cafcptg2011]

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..

[Oleg_Troyansky]

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!

[cafcptg2011]

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,

[maxgro]

if(SalesDate=StoreDate, 1)

[Oleg_Troyansky]

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.

[Gysbert_Wassenaar · Accepted solution]

Take 2. See attached qvw.