Dear all,
Still exploring the basics of QS and bumped into the issue which i am trying to resolve.
I am using the first value function over a simple data set, which is shown on the table below:
The function in the E column returns the first sorted value across whole table
Question - is there a way of calculating the first sorted value only from the specific data range? For example, where the Flag=1
Let me know please what your thoughts are
Many thanks!
Yes, you can use the relative date filter to apply two dates for filtering data, as shown in the image. The firstValue function will select values based on the applied filter.
For example, based on the screenshot you posted, if you have a field called “flag”, you can filter by a “1” value or apply a date filter between 04-01-2024 and 08-01-2024. The firstValue function will then pick the value 160.
Thanks!
But it’s not exactly what i need. The way of filtering you propose is on the whole table, but i need just the firstsorted value to reach on that filter.
Further more, the first sorted value will be a part of another calculation, so i was thinking if there’s a way of setting up the filter in the formula itself like firstvalue(ifelse(Flag=1, Avg({Amount})), [Date ASC])
Could you please confirm that there’s no such a possibility in QS?
If you need to get the first occurrence of a value when Flag = 1, try the following calculation to get the exact results as shown in your screenshot.
Example: (Syntax may vary - replace the fields from your dataset)
firstValue(ifelse(Flag = 1, Amount,0 ), [(ifelse(Flag = 1,Date,parseDate('01-01-2050', 'dd-MM-yyyy')
)) ASC])
Thank you!
I have a better idea on how the if statement can be included as the filter.
However, using the below formula that goes through the Date (Main) field, it doesn’t work
firstValue(Avg(ifelse({Forecast Balance Flag} = 1, {Forecast Balance},0)), [(ifelse({Forecast Balance Flag} = 1,{Date (Main)},parseDate(‘01-01-2050’, ‘dd-MM-yyyy’)
)) ASC])
Can you add {Forecast Balance Flag} field to the Group By and then check
Unfortunately, i cannot, as eventually i will use this formula in the line chart metric, where only Date (Main) field will be used in the X axis.
That’s why i am trying to workaround the calculation with the AVG, which for some reason still doesn’t work
Can you please explain what you are trying to do in the Line Chart? I can then try to help you achieve what you are looking for.
If you can recreate this issue in Arena using sample non-sensitive data, I can take a deeper look to see what is going on.
Will do first thing in the morning 
Thank you so much!
Hello,
Sorry for the late reply
On the picture below i reproduced the whole calculation which i eventually need in QS:
A few details how G & H columns are calculated - they are combined of 2 calculations:
By the link below you can access the dashboard i made in Arena, hopefully you can see it and deep-dive into the dataset view:
Many thanks for your support and look forward to hearing from you!
Think i am very close to achieve what i need.
Apart from the main graph which contains running total movements only, i have also calculated the start balances for both actuals and forecast in straight table (the last 2 columns):
The problem is that these calculations should work with the TransactionDate field only, so i guess, somehow in the aggregated format.
So, the start balances needs to be included in the line chart metric
Let me know please if you can find some solution
Please try the following formulas for the Actual Position and Forecast Position. They match exactly the same values you posted in this thread. Unfortunately, you can’t use these calculated fields in a line chart because they are visual-level calculations and multiple fields are associated with them.
Example:
Actual Position - ifelse({Actuals Start Balance} <> 0, {Actuals Start Balance} + runningSum(SUM({Actuals Amount}),[TransactionDate ASC],[BankAccount]), 0)
Forecast Position - ifelse({Forecast start balance} <> 0, {Forecast start balance} + runningSum(SUM({Forecast Amount}),[TransactionDate ASC],[BankAccount]), 0)
Hi!
Thanks for sharing this
My goal is to get the line chart with:
I can calculate them now in the straight table, but with the current formula version, they require the presence of other fields like Balance Actuals, Actuals Amount, etc.
So in short, i am looking for the solution how the firstvalue formula can calculate exactly the same Actuals and Forecast Start Balances with ONLY 1 dimension - TransactionDate.
Sorry, I’m out of ideas. I would recommend filing a case with AWS Support where we can dive into the details so that we can help you further. Here are the steps to open a support case. If your company has someone who manages your AWS account, you might not have direct access to AWS Support and will need to raise an internal ticket to your IT team or whomever manages your AWS account. They should be able to open an AWS Support case on your behalf. Hope this helps!
Many thanks and we appreciate your support.
Once the case is resolved, i will immediately let you know
You’re very welcome. I appreciate your prompt responses and look forward to hearing from you once the case is resolved.
