Quicksight - Insight visual

Q&A
, ,
1

Need to build insight visual for given two months. visual like period to date, top mover, bottom movers.

in dataset having startdate field as date datatype and another StartYearMonth field string as “year-month” from startdate field as string, I have created two string parameters get values from StartYearMonth field.

I tried to create below calculated field using lag function but getting error at datediff part.

lag(
sum(total),
[startdate ASC],
dateDiff(parseDate(concat(${StartDateyearmonth},‘-01’),‘yyyy-MM-dd’),parseDate(concat(${EndDateYearMonth},‘-01’),‘yyyy-MM-dd’), “MM”)
)

How to fix the issue or any other way to give the insight visual with two dates (year & Month only)?

Dynamic lookup index in lag calculation
2

Hi @SK20 and welcome to the Quick Suite Community!
In regards to your calculated field error; when using the lag function, you cannot use a dynamic value/row to base your calculation off of; it provides the variance based on a lookup index, which has to be a set number. So you would need to create some type of ranking first, that way you can utilize the lookup index to refer to another row.

3

Thank you for replying @Brett . Can you please provide some inputs on the ranking first.

4

able to archive the soultion using sumIf.

1 Like