topic Re: Re: Calculate the relative percent in a line chart in QlikView

Calculate the relative percent in a line chart

bdiamante — Mon, 11 Aug 2014 21:57:08 GMT

This seems like it should be simple... I have a simple line chart showing the percent of the total population each ethnicity represents. Or at least that's what I want. But there doesn't appear to be an easy way to calculate this percentage. The graph below gives a general idea of what I'm trying to do, but you'll notice that for each year, the different ethnicity populations do not total to 100% because the the "relative" option just computes a TOTAL over all dimensions, which since I have both ethnicity and year as dimensions, is not correct.

How can I tell the chart to compute the percentage for EACH YEAR rather than over all years??

Re: Calculate the relative percent in a line chart

Tue, 12 Aug 2014 00:43:47 GMT

Hi Brian,

See attached example.

Basically I do this: sum(yourfield)/sum(Total <Year> yourfield)

The 'Total <Year>' will ignore ethnicity as a dimension and allow you to sum all records per year, which you need to so you can create a proportion that adds to 1 per year.

Hope that helps.

Matt

Re: Calculate the relative percent in a line chart

jagan — Tue, 12 Aug 2014 01:28:08 GMT

Hi,

Try this expression

=Sum(MeasureName)/Sum(TOTAL<Year> MeasureName)

This will calculate the percentages by Year, ignoring the Ethnicity_Code dimension. Hope this helps you.

Regards,

Jagan.

Re: Calculate the relative percent in a line chart

Tue, 12 Aug 2014 18:34:37 GMT

Hi Brian,

Were you able to resolve this?

Best,

Matt

Re: Re: Calculate the relative percent in a line chart

bdiamante — Tue, 12 Aug 2014 18:50:45 GMT

Not exactly. I think something else might be impacting what's happening here. I actually had my expression set up to do exactly what the other commenters suggested:

sum({Global<subject_code=, assessment_code=> * HomeSchool}number_ethnicity_gender_enrolled) / sum({Global<subject_code=, assessment_code=, data_year=> * HomeSchool} TOTAL number_ethnicity_gender_enrolled)

I use states pretty heavily in this dashboard. So it's a bit convoluted...

The Global state:

The HomeSchool State

The population that's left after the set analysis is a single school's records, for all years. I thought that the above formula would work as well, but it hasn't and I've tried a few different variations, but still cannot get the correct percentage...

Re: Re: Calculate the relative percent in a line chart

bdiamante — Tue, 12 Aug 2014 18:59:20 GMT

My mistake, I did not have the formula you suggested. It's amazing the tricks your eyes can play on you after a long day... So having the TOTAL <data_year> before my set analysis did the trick...

sum({Global<subject_code=, assessment_code=, data_year=> * HomeSchool}number_ethnicity_gender_enrolled) / sum(TOTAL<data_year> {Global<subject_code=, assessment_code=> * HomeSchool} number_ethnicity_gender_enrolled)

I was not aware TOTAL was being treated as a set, I just thought it was a keyword. Is that what's happening here?

Re: Re: Calculate the relative percent in a line chart

Tue, 12 Aug 2014 20:30:58 GMT

Good to hear, glad it worked out.

best,

Matt

Re: Calculate the relative percent in a line chart

Dan36 — Tue, 09 May 2023 05:43:22 GMT

This worked well - cheers