Hi Everyone,
I had a dashboard, that was getting the values I needed for each year through a paramater where I selected the year and the followin code.
CY:
IF [Par. Año] = YEAR([FECHA])
THEN 1
END
PY:
IF [Par. Año]-1 = YEAR([FECHA])
THEN 1
END
The issue is that we have decided to change the approach and now we are going to have all the data for different times of the year, but we only wanto to get the data for the most recent date, for each of the different values on the Nombre Column (Data attached).
Has can be seen in the dashboard image, we have the following.
- Up top a count of the number of sites for the selected year and the difference with the previous year. (and a couple other meassures). Here I would like to only count the sites within the year selected by the parameter, but not count them twice or 3 times due to having several times the site because of different yearly meassures
- A chart with the number of sites for each year (based on name) with the quality values. Here I need for each site to take the quality of the max date. right now is taking the value
- a map where I would like to put a dot for each location, with the colour of the dot based on que water quality of it's last reading.
How do I use LOD to get the lates reading for each location on the above calculated fields, and also the below.
GOOD QUALITY
SUM(IIF([Calidad]='Buena',[Nº Zonas de baño CY],0))/SUM([Nº Zonas de baño CY])
this one worked when I only had one reading per year, but now of course it wont.
Many thanks,
Javier
//Is Max Date for Year and Location
[Fecha] = { FIXED [Nombre], YEAR([Fecha]): MAX([Fecha]) }
The equivalent of your calculation in your calculation in your question would then be:
IF [Is Max Date for Year and Location] THEN 1 ELSE 0 END
But I wouldn't bother using that. I'd just filter the data to the two years (and locations) I wanted to display, filter [Is Max Date for Year and Location] to True values, display the Measure(s) you want, and use table calculations for your 'versus last year' values. Doing it that way means you don't need to create any additional calculated fields for other Measures.
Alternatively, if you really want specific CY and PY fields for every Measure you want to display, you can still use:
// CY Measure
IF [Is Max Date for Year and Location] AND YEAR([Fecha]) = [Par. Año] THEN [Measure] END
// PY Measure
IF [Is Max Date for Year and Location] AND YEAR([Fecha]) = [Par. Año]-1 THEN [Measure] END
