Top Movers Insight multiple days

[Andrea_M]

Hi All,
is it possible in the top movers insight compare the last day with the max of the last three days?

Example:
Today, 100
Yesterday, 80
Yesterday - 1, 75
Yesterday - 2, 110

Now the the top movers calculates the difference between today and yesterday and gives an absolute different of +20.
What I’m looking for instead is a comparison between the MAX of the range yesterday-(yesterday-2), so the results should be -10.

Probably I could find a way changing the query or using a calculated field adding a new field with the max of the last 3 days for yesterday while the same value for today.

Ie.
Today, 100, 100
Yesterday, 80, 110

Let me know.

Thanks

[Max]

Can you do a rank of your value field and filter only to today or a rank of 1. The rank would also take into account that it is not today. And I would also add a filter that makes sure it’s only in today, yesterday, yesterday -1, and yesterday -2.

This is really long because it needs to be an aggregation over days.

rank_field = rank([ifelse(dateDiff(firstValue(truncDate(‘DD’,{event_timestamp}),[truncDate(‘DD’,{event_timestamp}) ASC],[truncDate(‘DD’,{event_timestamp})]),firstValue(truncDate(‘DD’,now()),[truncDate(‘DD’,now()) ASC],[truncDate(‘DD’,now())]))=avg(0),sum(-1),count({field_value})) DESC])

This is checking if it’s today, if so then it is summing on negative 1 to get it as a very high rank. Else it is counting the field that you want.

Again, what I would do is filter to today or this field of 1. and that it’s only in the past 4 days.

Hope this helps.

[Andrea_M]

So conceptually I should do a rank for the value on a rank used to take the last 4 days - 1.

Also, which is the best way to set a threshold on the insight?
So like exclude from the narrative all the movers where the difference is lower than x, so if there’s nothing greater than x should be “No top movers”

[Max]

You can do an if statement in the insight.

If it’s not then it won’t return anything.

You can also an an ifelse as well which might work better for you with your No top movers.

[Andrea_M]

The problem I think was that done in the items “for” the first part will be printed in any case.

[Max]

Yeah I would look to do if statements. You also might want to look at hard coding the index.

TopMovers.items[2].absoluteDifference > .05.

Something like that ^

[spaluri]

Hi @Andrea_M - Did Max’s solution work for you? I am marking his reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!

[Andrea_M]

Hi all, so sorry for late reply but I had temporary stock this analysis.

I still have a problem:

I’ve create this calculated field

ifelse(
denseRank([truncDate(‘DD’,{timestamp}) DESC]) = 1,
sum({value}),
maxOver(max({value}))
)

This returns for today the current value while for the prev days the MAX.
In order to limiting the days to be considered in the MAX, i’ve filtered on the last 4 days in the timestamp.
It’s working properly for the cases like:
Today, 100
Yesterday, 80
Yesterday - 1, 75
Yesterday - 2, 110

It returns:
Today, 100
Yesterday, 110
Yesterday - 1, 110
Yesterday - 2, 110

So the insight is ok.

The problem is when today is the MAX, so like:
Today, 110
Yesterday, 80
Yesterday - 1, 75
Yesterday - 2, 100

In this case, the yesterday value will be always the same, so no movers.
Today, 110
Yesterday, 100
Yesterday - 1, 100
Yesterday - 2, 100

How I can remove today’s value from the MAX calculation?

Thanks

Edit: This seems working:

ifelse(
denseRank([truncDate(‘DD’,{timestamp}) DESC]) = 1,
sum({value}),
maxOver(ifelse(
denseRank([truncDate(‘DD’,{timestamp}) DESC]) = 1, 0,
max({value})))
)

[Andrea_M]

About the way to set a threshold, I’m not finding how to print “No Top Movers”.

Also, I’m trying to:

• in the condition check both percentage and absolute difference so like or(percentage diff < -0.1, abs. diff < 100) but I don’t find any or condition
• add also the bullet point in the if.

Any hint?

[Andrea_M]

I did progresses on this:

• i’ve simulated the list
• i’ve set the threshold “properly”, managing also the no bottom movers
  
  

The problem now is that it seems to print empty list for all the excluded movers.
How I can avoid this?
I think the problem is the computation setting, but I don’t know how to change it.

It should be dynamic based on threshold, so even print 20 movers if it’s the case.