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: [image] The function in the E column returns the first sorted value across whole table Question - is…

Hi @AndriyShepel 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 ass…

Hi @AndriyShepel 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 “…

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 i…

Hi @AndriyShepel 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,…

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 (…

Hi @AndriyShepel 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

Will do first thing in the morning :slight_smile: 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 - F columns would be my dataset input G & H columns - these are the final calculations (we call them cumulative position metrics), which i need to represent in QS line chart o…

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): Actuals vs Forecast The problem is that these calculations should wo…

Firstvalue with the filter (on a specific date range)

Q&A

Xclipse November 7, 2024, 7:20pm 8

Hi @AndriyShepel

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.

Topic		Replies	Views
FirstValue doesn't work Q&A data-source , analysis , quick-sight , dataset , calculations , Business-Intelligence-Engineer	8	199	November 7, 2024
Display the value of one row when the other row is true Q&A quick-sight , calculations	4	779	May 11, 2023
特定値以下の最初の値の別の列の値を参照したい firstvalue 日本語で質問 \| Q&A analysis , calculations , Business-Intelligence-Engineer , Japanese	1	373	March 7, 2024
firstValue PRE_FILTER Q&A quick-sight , calculations	3	836	October 22, 2023
Finding and using first values Q&A analysis , quick-sight , calculations , Business-Intelligence-Engineer	6	251	May 21, 2024

Firstvalue with the filter (on a specific date range)

Related topics