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
And in same ways do the Latest Date - Calc format as Sep 20, 2023 in the values field well
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
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
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.
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.
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
@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
