Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
damago1
Contributor III
Contributor III

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

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:

Zrzut ekranu 2021-12-06 224235.jpg

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. 

 

2 Solutions

Accepted Solutions
Or
MVP
MVP

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.

View solution in original post

damago1
Contributor III
Contributor III
Author

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

View solution in original post

2 Replies
Or
MVP
MVP

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.

damago1
Contributor III
Contributor III
Author

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