Set analysis sur un champ date

[sebios]

Bonjour, 

Je voudrais faire un set analysis qui me renvoi le nombre de vente réalisé par un opérateur depuis la dernière date possible.

J'ai fais ce set  " =Sum({$<[TRL-DATMVT]={'=$(max(TRL-DATMVT)-1)'}>}[TRL-QTEUV]) " et le résultat me renvoi 0.

Alors que je devrais avoir 179 

Pouvez-vous m'aider svp ?

[Oleg_Troyansky · Accepted solution]

Hi,

I see a couple of issues that need to be looked at:

1. The expression max(TRL-DATMVT)-1 returns a numeric value, not a date, as you might expect. Enclosing it in a $-sign expansion with single quotes may not be enough. You probably need to add a date formatting function there. Also, I believe the Date field name with a minus sign requires brackets:

'=$(date(max([TRL-DATMVT])-1))'

2. Without applying any Search, your Set Analysis filter is looking for a single date - the max available date minus one day. I believe you want to select all dates that are higher than that date. For that, you need to add double quotes and the "greater than" sign, to turn it into a search:

[TRL-DATMVT]={" >'=$(date(max([TRL-DATMVT])-1))' "}

3. When no selections are made, your max available date is going to be the highest date in your data, so naturally you shouldn't expect to see any sales since that day. You will begin seeing any sales numbers only when selections are made to limit the highest available date to an earlier date. 

Try these changes. Ideally, post a sample app that we could take a look at.

Allow me to invite you to my Set Analysis session at the Masters Summit for Qlik, which is coming to Hamburg this September. You will learn a lot about Set Analysis and many more advanced development techniques.

Cheers,

[sebios]

Hello,

Thank you for your feedback. I confirm that I'm indeed looking for the last possible date -1 day. This application will be used for daily sending from nprinting, so I only need to have the last day. I just tried your set, but unfortunately, I still get 0 as a result.

It is not easy to put an application model, the rest of the data is rather sensitive.

[sebios · Accepted solution]

Hello,

I just ran this set analysis and it seems to work.

" Sum({<[TRL-DATMVT] = {"$(=Date(Max([TRL-DATMVT]), 'DD/MM/YYYY'))"}>} [TRL-QTEUV]) "

Thank you for your feedback.