Set Analysis : to identify the oldest value of a table depending on conditions and aggregated

[Patrick_Marseille]

Dear Qlik Community.

In a table Qliksense.

I would like to use the Set Analysis (by preference), but I want to avoid the script edition if possible.

To identify the value of the oldest Event of my selection, aggregated for each business unit (voyage).

The example is realated to a transport company. my final requirement is to calculate the consumption of products (gasoil)  based on stock differences.

The first step is to identify the first stock of each voyage, as described in the attached picture.

For each voyage, I have several Arrival. For the oldest arrival, I want to identify the step "FWE", (mean arrival i the factory), if the source is 'Arrival' and the product = 'A'.

In my picture, the result expected is in orange.

And the final result is to get one value for each voyage.

Thanks for your help !

Best regards.

Patrick.

[BrunPierre]

?

FirstSortedValue({<Event={'FWE'}, Source={'Arrival'}, Product={'A'}>} Stock, Event_Date)

[marcus_sommer]

I suggest to go always with a reversed approach and doing all essential work within the data-model and not in the UI. Doing it within the UI won't save efforts and being easier else usually the opposite happens by getting disadvantages by the performance and within the usability.

Personally I would tend to create appropriate flags and continuous counter and controlling with them the set analysis. The data may look like:

t1: load * from X;
     left join(t1)
     load KEY, min(Date) as X, max(Date) as Y, count(Date) as Z
     resident t1;

t2: load *, if(Date = X, -1, if(Date = Y, 1, 0)) as Flag;
     load *, if(KEY = previous(KEY), peek('Counter') + 1, 1) as Counter
     resident t1 order by KEY, Date;

[Patrick_Marseille]

Merci beaucoup Pierre.

Je n'avais pas essayé. ça marche, mais je dois tester encore et encore dans différentes tables et usages.

Je teste dans la journée.

Patrick.

[Patrick_Marseille]

Merci Markus.

J'ai un modèle de donnée compliqué, géré par mon département informatique. Et la valeur à agréger n'est pas sur la même table, et est assez éloignée.

Mon département informatique recommande un bon modèle de donnée + utiliser les Set Analysis (ou autre calcul).

Dans tous les cas, cette méthode via le script est bien notée, et si j'ai des difficultés avec le set analysis, je l'implémenterai.

Patrick.