topic Re: Chart comparing change over time for multiple dimensions - normalized at start (first value=1 for each dimension) in App Development

Chart comparing change over time for multiple dimensions - normalized at start (first value=1 for each dimension)

damago1 — Mon, 06 Dec 2021 21:53:32 GMT

I would like to create a graph comparing change in mortality for each vojevodship week by week, comparing to mortality in week=1 for each vojevodship. The absolute values are different because vojevoships are of different population but what I want to see is a growth/decline from the start of plotted period. That is I want to show only relative change for each week, compared to week 1. It means all lines should start with the same place, in week=1 and then show Sum(Mortality)/Sum("Mortality in week 1 for this vojevodship")

Here is a similiar graph showing a pair of currencies over time:

I have data with 2 dimensions: [week] and [vojewodship]. There are other dimensions too like [year] [gender] etc but this is not relevant here.

I have created a line chart. Added 2 dimensions to this chart, so I have now: Group=Week, Line=Vojewodship dimensions, and one measure: Sum(Mortality)

I was trying:

Sum(Mortality)/Top(Sum(Mortality)) but it is iterating over Vojevoships not over weeks. So it seems to be comparing to the first vojevodship in the same week and not to the first week in the same vojevodship

Sum(Mortality)/First(Sum(Mortality)) but it is returning nothing. The plot is empty.

Re: Chart comparing change over time for multiple dimensions - normalized at start (first value=1 for each dimension)

Or — Mon, 06 Dec 2021 22:22:09 GMT

There's probably a cleaner way to do this, but I think using a calculated, totaled set would work:

Sum(Mortality)
/
Sum(total <vojewodship> {< week= {"$(=vMinweek)"} >} Mortality)

Where vMinPeriod is a variable for the minimum week: =Min(Total <vojewodship> week) (if week is not a number, use MinString instead)

Note that this will only work if the data for all vojewodship starts on the same week. If there's a gap in the first week for any of them, this will return 0 across the entire line as there will be no first value found.

Re: Chart comparing change over time for multiple dimensions - normalized at start (first value=1 for each dimension)

damago1 — Thu, 09 Dec 2021 12:27:31 GMT

Thank you. This helped. At the end of the day I have created a bit different formula that works:

Sum(Mortality) / Sum(TOTAL <Wojevodeship> if(Week<20, Mortality,0))*19

I had some problems with {} based expressions and for some reason the {< week= {"$(=vMinweek)"} >} did not fully work, but the above line works like a charm.

The syntax:

Sum(TOTAL <Wojevodship> Mortality)

is a bit misleading because I thought this is summing over all Wojevodships and not sum ignoring Wojevodships