I want to make this field for plot marker in line chart for compare lag data and data is not same for show data that increase or decrease or no trend in change point only not all data and field field I use is text field compare
This is what logic I want to be
Hello @Sunpavit, you wouldn’t be able to plot the string value directly, but maybe you could do something that would create an int value based on the string.
My best thought would be to create a calculated field like this:
ifelse({Lag 1} = 'Increasing', 1, {Lag 1} = 'Decreasing', -1, 0)
You could do the same for your show column as well, then you would be able to plot the values as your y-axis to show the trends for each group. That is my best thought on managing this situation. I’ll mark this as the solution, but if you have any follow-up questions on this topic, please let me know!
in lag1 is not direct field but it is lag function that get from trend field to compare trend field .what i want is compare 2 field that dont’ have same value then show trend data but if have same value i don’t want to show but if lag 1 not have data then null
Hello @Sunpavit, I think I understand. My thought is to alter the first calculated field I posted slightly to check if the values do not match and lag1 is not empty, then return the value you want, otherwise, return null.
ifelse(isNotNull({lag 1}) AND {lag 1} <> "" AND {lag 1} <> {trend}, {trend}, NULL)
I’m not certain if your lag 1 field is returning NULL or an empty string when no value is given, so I checked for both in this statement. This way you can make sure it is a value and that the values do not match, then you can show the value you want.
Hello @Sunpavit, this is an error message that will likely appear often when building calculated fields. To give you a little more information, the calculation will throw an error if you are trying to compare a field that is aggregated to one that isn’t. That means, we will need to find a way to make the current trend value aggregated since using the lag function on the previous value is technically aggregated.
Since the lag function is labeled as a table calculation aggregation in the documentation, we will want to make another one of the calculations run on current trend without altering the output.
I would suggest something like lastValue since we are dealing with a string field, where we sort by [{datetime} ASC] like we are in the Lag function, and also partition by datetime so we get a value for each date like we are in the row. Something like this:
lastValue([{Trend (SMA)}], [{datetime} ASC], [{datetime}])
If the function throws an error for partition and sort being by the same value, you can try using truncDate on your partition to go by the HOUR value since that seems to be where your data is set to. This should help resolve the error though and I hope it makes more sense to you!
