Hi Team,
I have string value (Reason)- have status -start,stop,idle
And I have Datetime value (Timedefault)
Also used partition by (Machine)- Machine 1,Machine 2,Machine 3
I need to get the last Reason against each machine.So I tried last value function But when I tried to use this last value calculation in table ,I faced error as “Table calculation reference attributes are missing in field well”
Kindly give a solution or suggest any other method to get my latest reason by time
Hello @Marim, when you see the error Table calculation reference attributes are missing in field well, that means that your table is missing a necessary field that is being utilized by your calculated field to display the data you want. What you can do is start adding the fields you are utilizing in your calculated field 1 by 1, Reason, Timedefault, and Machine, until your data is displayed. Then, if you click on the 3 dots next to those fields in the field well, you can select to hide them from the table display. That way it will not impact how your visual is displayed, but it will allow you calculated values to show.
I’ll mark this as the solution, but if you have any follow-up questions, please let me know!
Thank you @WLS-DM
But if I use the calculated field in table , My last value correctly came but my records got duplicated value when I include my Reason into table.
If I did’t include my reason in table , my last value will not come. How to handle this?
Hello @Marim, what about if you select Hide field on Reason, and then add a filter for Reason to only display “Stop” since every machine seems to have “Stop” as a value. Does that give you the desired output?
No, I need current status of machine (idle,stop,start,breakdown),not only stop status.
Here The status are available in reason column
The status changing frequently, so I tried to get the last status by using last value function.
After that I used conditional formatting based on my last value column.
In the above document, For Machine 1 the current status is stop only, but if I include reason column in the table it both get current status (Stop) and previous status (Start). So I got two values against Machine 1 .But I need ony one record against each machine in the table.
Did you got my point? If you have any further clarification please let me know
Hello @Marim, my apologies, I’m not sure if I was clear.
You are able to hide a column from the display of the table without actually removing it. I am curious if you hide the reason field (an option that will show if you select the 3 dots next to the field name in the field well), if that will improve the visual display.
Also, if you apply the filter to only display Stop on the Reason field, which will now be hidden, it shouldn’t keep your Last Reason field from displaying the value you are looking for. It should just ensure you are only displaying a single row for each Machine.
Let me know if that makes a little more sense. I know some of these work-around can be a little confusing. If that doesn’t work, let me know any specific errors you experience. Thank you!
Hello @WLS-DM
I need to show the current status of the machine not only stop status.
In the below image, If you see machine 3 -the current status is idle which is updated correctly in the last value(4th column) but it will also get previous status as Start in the Reason(2nd column)
An for Machine 4 -the current status is start which is updated correctly in the last value(4th column) but it will also get previous status as Stop in the Reason(2nd column)
when I include reason column which I was used in my last value function,its display the multiple status against a machine which are changed between time value.
Is there any possibility to avoid duplicate value without filtering one status or please let me know if I need to try in any other method
@WLS-DM thank you for your response ,and please let me know if you have any other queries
Hello @Marim, isn’t your lastValue field the one that is returning the current status of the machine not the Reason field? From what I understand, your Reason field was only added to the table so the lastValue function could reference it. I could be wrong, but since the lastValue function is a table level aggregation, it should reference the Reason field before the filter on the actual visual for that field.
I also see though that Stop isn’t actually included on the reason for every machine, so that solution won’t work. In that case, I don’t think there is a way to remove duplicates with a calculated field. I think the only option would be to build your last value calculation for each machine in custom SQL and add it into your dataset directly. That way you could reference it in the table without the Reason field and avoid duplicates. I’m not sure what database you are using to build your datasets in Quick Sight, but you should be able to handle this calculation in SQL as well. I’ll link some documentation I found below:
Thank you @WLS-DM I will check this method
