FirstValue doesn't work

Question & Answer
, , , , ,
1

Hi team

I am building a simple report with the calculated field, which should populate the start balance taken from the amount field sorted by the transaction date per each account

Calculation:

firstValue({Amount}, [{Transaction Date} ASC], [{Account}])

As an output, i am getting Unavailable value:

image
image2252×734 43.3 KB

Any suggestions why this doesn’t work?
Thanks

2

Hi @AndriyShepel

firstValue function calculates the first value of the aggregated measure or dimension partitioned and sorted by specified attributes.

You can verify it in the Table visual by adding the measures and dimensions within the analysis.

Example: (Replace the fields from your dataset)

firstValue(
    Amount,
    [{Transaction Date} ASC],
    [
        {Account}
    ]
)

image
image860×447 37.2 KB

1 Like
3

Many thanks for your reply
I’ve simulated the scenario with the proposed formula and it works

firstValue({Balance Amount (Actuals)}, [{Date (Joined)} ASC], [{Account (Joined)}])

image
image1118×1022 63.3 KB

Question - how can i keep the first value still visible if i drop the Amount field from the table?
(got an error: “Table calculation attribute referrence is missing in the field wells”

4

Hi @AndriyShepel

You can hide the fields after adding it to visual Table or Pivot Table.

image
image872×492 36.1 KB

1 Like
5

Thanks, that sounds like a workaround (it works)
But why do i need to hide the column in order to have the calculation happening?

From what i understand, the function takes the first sorted amount based on the date sorting and groups the calculation on the account field. Why does it fail if the “Balance amount” field is not added to the grouped dimensions?

6

Sorry to disturb you on this, but i would like to share with you the final case i am gonna build:

i will need to add the start balance to already pre-calculated running totals - the line chart has only one date axes:

image
image1898×1072 108 KB

7

Hi @AndriyShepel

Aggregate functions for calculated fields in QuickSight including the firstValue. These are only available during analysis and visualization. Each of these functions returns values grouped by the chosen dimension or dimensions. For each aggregation, there is also a conditional aggregation.

Please refer to the below documentation this might be helpful for you.

8

Many thanks for clarification.
And the last question in this topic - is there a way to apply a formula filter on which date range the firstvalue function earch the value?

for example, i want to to go through the date range where the Field_1=1?
Thanks

9

Hi @AndriyShepel

I’ve answered the same question that you posted in QuickSight community portal.

I hope this helps!