How to avoid duplicate lines

[Bintou15]

Hello,

I'm coming to you for a little problem with the display on Qliksense. I have in a table(ProductionUnitActivityEventTimestamp) the production flow for each engine serial number (unique), it tells us at what time it does an activity (painting, oil change). I need 3 infos in this table:

Activity (the last activity of the engine) -> it's the activity done at the most recent date (in the Regtime column)

Real start date -> it's the date when the engine started the 1st activity

Real delivery date -> it is the delivery date of the engine, when the engine goes through the activity 12

 

I have calculated these two fields in this table: 

Real start date: if([GASS_ProductionUnitActivityEventTimestamp.ACTIVITYNO] = 1, if(REPORT_TYPE = 'START', REGTIME_DDMMYYY)) -> to retrieve the start date of the engine activity

Real delivery date: if([GASS_ProductionUnitActivityEventTimestamp.ACTIVITYNO] = 12, REGTIME_DDMMYYYY) -> to retrieve the delivery date when the engine is in activity 12 (which corresponds to the shipping of the engine)

AND in the sheet I have this as display for each engine:

 AND I want on a single line the delivery Unit, its last activity (here  Expedition), the Real start date (here 24/10/2022) and the Real delivery date (here 02/11/2022) except that it creates several lines.

I used this as a formula for Activity: =if(REGTIME = Max(total(REGTIME)), [Activity description])  -> if the date in REGTIME is the most recent I take the activity that corresponds to this date.

Do you know how to avoid this lag please, thanks a lot?

[Bintou15]

Hello,

I'm coming to you for a little problem with the display on Qliksense. I have in a table(ProductionUnitActivityEventTimestamp) the production flow for each engine serial number (unique), it tells us at what time it does an activity (painting, oil change). I need 3 infos in this table:

Activity (the last activity of the engine) -> it's the activity done at the most recent date (in the Regtime column)

Real start date -> it's the date when the engine started the 1st activity

Real delivery date -> it is the delivery date of the engine, when the engine goes through the activity 12

 

I have calculated these two fields in this table: 

Real start date: if([GASS_ProductionUnitActivityEventTimestamp.ACTIVITYNO] = 1, if(REPORT_TYPE = 'START', REGTIME_DDMMYYY)) -> to retrieve the start date of the engine activity

Real delivery date: if([GASS_ProductionUnitActivityEventTimestamp.ACTIVITYNO] = 12, REGTIME_DDMMYYYY) -> to retrieve the delivery date when the engine is in activity 12 (which corresponds to the shipping of the engine)

AND in the sheet I have this as display for each engine:

 AND I want on a single line the delivery Unit, its last activity (here  Expedition), the Real start date (here 24/10/2022) and the Real delivery date (here 02/11/2022) except that it creates several lines.

I used this as a formula for Activity: =if(REGTIME = Max(total(REGTIME)), [Activity description])  -> if the date in REGTIME is the most recent I take the activity that corresponds to this date.

Do you know how to avoid this lag please, thanks a lot?

[vinieme12]

Try using Max() 

Max(datefield),   or Max(TOTAL <Activity> Datefield)

your screenshots are too small in-size , can you post some sample data

[Bintou15]

It works by using Max total( <Activity> ..), I get the last activity. The problem is the field < Real start date > that displays on another line instead of displaying on the same line as the other fields.

 Please find the sample date attached, I explained What I have and what I would like to get  at the end in a sheet

[vinieme12 · Accepted solution]

it appears on a different row because your ActivityDescription is on a different row

use MaxString() 

ActivityDesc = MaxString({<ActivityNo={12}>}ActivityDesc)

StartDate = Max({<ActivityNo={1}>}RealStartDate)

DeliveryDate = Max({<ActivityNo={12}>}RealDeliveryDate)

[Bintou15]

Hello, first thanks for your quick response.

I copied-paste your formula: 

=MaxString({<ACTIVITYNO={12}>}[Activity description])

= Max({<ACTIVITYNO={1}>}[Real start date])

=Max({<ACTIVITYNO={12}>}[Real delivery date])

 and get this error: dimension not valid for each of the three calculated fields. 

Did you something else before calculating these fields?

If the last activity of the DELIVERYUNIT(engine) is not 12 but 9 for example, will your formula still work?

Here is the formula I used first to get the last activity for each DeliveryUnit:

Last Activity: =if(REGTIME = Max(total(REGTIME)), [Activity description]) 

-> if the date in REGTIME (this fields says the date and time when the engine started/finished the activity mentionned) is the most recent one I take the activity that corresponds to this date.

but I get two lines each time, the first time is the correct one and the second is blank. I don't know why.

 

thanks

[vinieme12]

these are MEASURES  not dimensions

but you can add them as Calculated dimensions by wrapping them in AGGR() as below

=AGGR(MaxString({<ACTIVITYNO={12}>}[Activity description]) , ACTIVITYNO)

= AGGR(Max({<ACTIVITYNO={1}>}[Real start date]), ACTIVITYNO)

=AGGR(Max({<ACTIVITYNO={12}>}[Real delivery date]), ACTIVITYNO)

If the last activity of the DELIVERYUNIT(engine) is not 12 but 9 for example, will your formula still work? YES, just change the filter values in set analysis

read more on set analysis here

https://www.analyticsvidhya.com/blog/2014/01/set-analysis-qlikview/

[Bintou15]

yes You are right they are measures, I get what you showed. 

when I wrapp them in aggr(), it still shows two lines (the correct one and a blank one), exactly what I had in the first time with my formula (Last Activity: =if(REGTIME = Max(total(REGTIME)), [Activity description]) )

 

I am gonna read about set analysis to find a solution.

[vinieme12]

In your Chart You have tj option to add Dimensions and Measures

The expressions that I posted wrapped in Aggr() NEED TO BE ADDED AS DIMENSIONS 

Add Dimensions >> then add below formula 

=AGGR(MaxString({<ACTIVITYNO={12}>}[Activity description]) , ACTIVITYNO)

THE ONES WITHOUT AGGR() CAN BE ADDED AS MEASURES 

Example

Dimensions 

ActivityNo 

=AGGR(MaxString({<ACTIVITYNO={12}>}[Activity description]) , ACTIVITYNO)

= AGGR(Max({<ACTIVITYNO={1}>}[Real start date]), ACTIVITYNO)

=AGGR(Max({<ACTIVITYNO={12}>}[Real delivery date]), ACTIVITYNO)

Measure

Count(Activityno)

OR 

Dimension 

Activityno

Measure 

MaxString({<ActivityNo={12}>}ActivityDesc)

(Max({<ACTIVITYNO={1}>}[Real start date])

Max({<ACTIVITYNO={12}>}[Real delivery date])

hope this is clear enough 

[Bintou15]

Yes it' clear thanks. 

By using measures, 

MaxString({<ActivityNo={12}>}ActivityDesc)

Max({<ACTIVITYNO={1}>}[Real start date]) 👍

Max({<ACTIVITYNO={12}>}[Real delivery date]) 👍

 it works, I get the real start date, the real delivery date.

When you say: If the last activity of the DELIVERYUNIT(engine) is not 12 but 9 for example, will your formula still work? YES, just change the filter values in set analysis

-> you mean that I need to change manually the filter?

Because I can't know in advance what the last activity can be, it could be 9, 12, 31 etc

Now I am looking for a way to get the last activity whatever it is.

[vinieme12 · Accepted solution]

For that use firstsortedvalue()

Example

=Firstsortedvalue (ActivityDesc, -Datefield)