I have a dataset that have the information about buildings.
For every building i have a main table and several table connected.
I need to calculate the average of a filed (specific_thermal_consumption) in a table without consider duplicate value and using only non zero value.
For clairifcation assume that my dataset is the following “table”
Building with Id, usage field and other value
Board with thermal_consumption
Energy_Carrier with some other value
Building and board are 1:1 relation
But building and energy_carrier have 1:N relation
I created 3 calculated field
C1 : firstValue(
{specific_thermal_consumption},
[{building_internalreference[board]} ASC],
[{building_internalreference[board]}]
)
C2: ifelse({C1} >0, {C1},NULL)
C3: avgOver({C2},[usage])
I tried to use C3 in a table but i get an error: VISUAL_CALC_REFERENCE_MISSING
FirstValue calcs are table based calculations and need refencing field to be available on the visual.
What is your data source ?
If its database, try creating a rank/row-number function for building and energy_carrier have 1:N relation and the rank/row-number 1
In QuickSight you can add calc to filter energy_carrier rank 1 and non-zero values.
Hi, my datasource is a database, but adding a field in database is not a solution.
The duplication of the row happen in spice.
For example if I have one building with 4 energy carrier I obtain 4 row with the same data board.
Also I added a referencing field in the first Value function, but I need it only to obtain the first value and exclude the duplicate
Acter your response I come out with a different idea.
Add a calculated field (number of occurrence of internal referemce ) that is based on the internal reference of the building. And the use a single calculated field with two if else one for nullify the zero data, and another one for nullify all the data that correspond to a rank greater than one. A solution like that can work?
If yes how can I calculate the occurrence of internal reference?
@LucaNitti are you still running into this problem or were you able to find find a solution? Or was @Ashok response helpful in finding a solution? If so could you post your workaround to help the community or mark one of the comments above as the solution?
