I Need Help I want to make This marker have color base on Filed Trend(SMA) How do I do?
Hey Sunpavit,
I’m pretty sure your goal is not possible with a line chart, since you can usually only use one color per line.
But maybe you can recreate your intend with a bar chart and two Ifelse - Amazon QuickSight fields and Lag - Amazon QuickSight function. I haven’t tried it yet and would need some time to test it.
Br
Robert
If I use parameter to filter for show the goal I want to can it be work?
for example select increasing , decreasing and no trend show only increasing is green ,decreasing red and no trend yellow data
Yes, i think that can work with overlaying dashboards. I can also try to look into it, it seems like an interesting problem.
I can show you a different solution it’s exactly what you wanted but maybe it can help you:
To show a positive bar or line like in the right side I used this calculated field:
ifelse(lag(sum(Profit), [{Order Date} ASC], 1) < sum(Profit), sum(Profit),
NULL)
for negative use > or = for equal
In the upper right graph, the positive and negative values overlap the normal line. Sometimes you only see the data points and not the lines, I’m not sure how to change this yet.
At the bottom right you see a combined bar and line chart, the bar shows the increase in green and the decrease in red. The line is not exactly in the middle, but you can use two overlapping charts as a workaround to center the line.
Hopefully this can help you.
BR
Robert
1 Like
Now i found a better solution, that should work for your use-case:
You just need to add lead to your calculated field:
ifelse(lag(sum(Profit), [{Order Date} ASC], 1) < sum(Profit) or lead(sum(Profit), [{Order Date} ASC], 1) > sum(Profit), sum(Profit), NULL) for increase
ifelse(lag(sum(Profit), [{Order Date} ASC], 1) > sum(Profit) or lead(sum(Profit), [{Order Date} ASC], 1) < sum(Profit) , sum(Profit),NULL) for decrease
if there is no change you only need to change the signs to =
1 Like
I tried and I think it’s very good but I’ve to check with my senior for make sure this is what he want THX A lot Robert
Ball.
And sorry I replied you late because I stuck My other Project And Thank you for your Help.
Ball.
Robert this Formula is almost perfect. My Senior check and He want to use similar your formula but change to compare more or less to compare lag data and data is not same for show data that increase or decrease change only not all data and field he want to use is text field to compare with lag
This is what logic he want to be
THis is formula that I modified from yours and what i think from his logic
ifelse(lag({Trend (SMA)},[datetime ASC],1)<>{Trend (SMA)},{Trend (SMA)},NULL)
but i stuck this error
WHat Should I do?
Ball.
Hey Sunpavit,
you need an aggregation for {Trend (SMA)}, sum({Trend (SMA)}) for all the points should do it.
Regard
Robert
Ok, I read you text again and understand it now better. In the Ifelse you should still use the value that increases or decreases, aggregation of strings is sort of complicated, and ad “Increase”, “Decrease” at the end:
ifelse( lag(sum(Profit), [{Order Date} ASC],1) < sum(Profit), “Increase”, “Decrease”)
That should work for trend, for lag 1 you need to compare offset 1 with offset 2:
ifelse( lag(sum(Profit), [{Order Date} ASC],1) < lag(sum(Profit), [{Order Date} ASC],2), “Decrease”, “Increase”)
Maybe this can help you
Regards
Robert
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
Hey Sunpavit,
I was busy at the end of the week, if you still need help:
it should look something like:
ifelse(
lag1 = NULL, NULL,
lag1 <> trend , trend,
‘0’)
The 0 must also be a string that’s why we use ‘0’
1 Like