Last event/ date calculations

Sorry still pretty new to quicksight, I only need last “event_dt” for a given “Equipment Number”. Not all dates and events.

I also need help with a calculated field, The days between the last “event_dt” and whatever the current day is.

@aaron000000001 - Thank you for posting your query. When you say last event_date do you mean maximum or latest event_date? Can you please provide some details around it? Thanks!

1 Like

hi @aaron000000001 for your analysis we need to create 3 calculated field just try this one if you facing any issues please let me know.

First Calculated Field for calculating Latest Date on the basic of equipment number

1. Latest Date - Calc

maxOver(max(event_dt),[])

2. Current Date - Calc

Now()

Note - Now function will give you the current Date

3. Date Duration Between Current Date & Latest Date - Calc

dateDiff(max({Current Date - Calc}),{Latest Date - Calc},‘DD’)

Note - Here we are getting the duration as Day

Note - Put the Latest Date - Calc & Date Duration Between Current Date & Latest Date - Calc in the values field well and the Current Date - Calc** in the rows field well to get the desired result.

And also change the format for Latest Date - Calc & Current Date - Calc as below ways .
Click the Current Date - Calc in the rows field well and select format as Sep 20, 2023

image

And in same ways do the Latest Date - Calc format as Sep 20, 2023 in the values field well

image

These are the steps you need to follow.

1 Like

Thanks For the info, I tried Latest Date- Calc but its all giving me todays date
I also tried making the 3rd calculated field you suggested but kept getting syntax errors, even though it looked identical to yours.

it would be the latest event_date, thank you!

hi @aaron000000001 Please check once with your calculation because I am trying at my end it is working fine for me if you faced the same issues then please update so that we will check & get back to you.

1. Latest Date - Calc

maxOver(max(event_dt),[])

1 Like

maxOver(max({event_dt}),) is what I have for latest Date

is there supposed to be a value in “”?

I think better to keep the same formula just place in the square bracket as equipment_number field
maxOver(max(event_dt),[equipment_number])

Yeah that’s what I used but I am still getting all current days
image

Yes I just posted the formula ,

Please put in the square bracket as equipment_number like this,

maxOver(max(event_dt),[equipment_number])

Please use this formula and let me update it is working for you or not.

image

Unfortunately I don’t think that worked either, sorry for all the trouble

1 Like

Hi @Max can you please help on this we are tying to do some calculations but not able to get the result .

1 Like

Ya no issue thanks for your reply I am trying at my end its working fine but I think some different formula will works for you I tagged max with this query he definitely help us.

If you see I used the same formula its work for me.

image

Looking at your data, you should put the equipment date and event code as well, can you try that…

1 Like

Something like above.

Regards -San

1 Like

That seemed to almost work!
the only thing now in the example. event code A,D,W,X,Z is arbitrary old information I would only need to show event code P in this case
image

@aaron000000001 You can put a filter on event_code and only select P ones…

Hope this helped you.

Regards - San

1 Like

I guess I didn’t phrase that well, the latest date is not always P it could be one of the other event codes. I only need to show the latest event. If that makes it clear.

In this example I would only need to show Code J because its Mar 10

image