LAG function returning "unavailable"

Q&A
,
1

I have five columns in my dataset: Location, Vendor, Week, Number, Value. I’m trying to return the previous value for the Value field, sorted by week + number (both DESC), and partitioned by location + vendor.

My first thought was to use the Lag function and just grab the previous row, but when I try to create this field in the dataset, I just get “Unavailable” for the whole column. If I try to strip out the complexity and just grab the previous Value, with no sort or partition, I get a syntax error.

Does anyone have any advice on how to get this function set up properly? Thanks!

2

Hi @khuebsch ,

welcome to the community :tada:

Can you post the formula in here? I regulary do mix up the brackets in the formulas.
In accordance with the four-eyes check (or multiple-eyes check :wink: ).

Or you upload your data (or a sample) and formula to Quick Sight Arena (Announcing QuickSight Arena: Explore Amazon QuickSight for free and showcase your dashboards | AWS Business Intelligence Blog).

Best regards,
Nico

3

Hi Nico, sure thing! Here’s the formula:

lag({value}, [{vendor} ASC], 1, [{week_number}])

This returns only “Unavailable”

Stripping out the sort and partition pieces, to this:

lag({distractions_rate}, , 1,)

Returns a syntax error, just stating the syntax of the calculated field is incorrect, and that I must fix that first.

Thanks for your help!

4

Hi @khuebsch ,

Thank you for the formula. That seems good.

I read your first post again. You are building the formula in your dataset: “to create this field in the dataset”

The lag function is a table calculation, please try to create that field in an analysis. Before you add your formula to the analysis, add the fields to your visual and the the calculated field.

Best regards,
Nico

Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!

5

Hi @khuebsch,

Following up here as it’s been awhile since we last heard from you on this thread; did you have any additional questions regarding your initial post or were you able to find a work around?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you

6

Hi @khuebsch,

Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community.

Thank you!