Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Use of population per Financial Year

Hi All

I use GP registered population to identify a population and also "cost per head" in certain locations, however, I need the charts to change according to the financial year selected in the Dashboard, and therefore also the calculation of the "cost per head".

I am unsure which is best way to set out the population data, ie:

(1) have the GP practice info going down the spreadsheet with the financial year running across the top and the populations in the same columns as the relevant financial years:

GP Practice NameNetwork2012/132013/14
Practice 1Network 1100110
Practice 2Network 1120100
Practice 3Network 280100
Practice 4Network 26080

If I use option (1) what is the best way to calculate the "cost per head", given that I want the calculations to change according to the financial year selected?

(2) have the GP practice info going down the spreadsheet, per financial year:

GP Practice NameNetworkPopulation
Practice 1Network 1100
Practice 2Network 1120
Practice 3Network 280
Practice 4Network 260
Practice 1Network 1110
Practice 2Network 1100
Practice 3Network 2100
Practice 4Network 280

Given that I want the calculations to change according to the financial year, I'm using this the following easy calc, but its not working right:

sum([Cost £])/sum(Population))

Any advice would be gratefully received!

Thank you

Claire

7 Replies
sunny_talwar

I would say use the second method with set analysis to restrict data. May be like this:

For 2013/13

=Sum({<YEAR = {'2012/13'}>} [Cost £])/Sum({<YEAR = {'2012/13'}>} Population)

For 2013/14

=Sum({<YEAR = {'2013/14'}>} [Cost £])/Sum({<YEAR = {'2013/14'}>} Population)

MK_QSL
MVP
MVP

SUM(Aggr(sum([Cost £])/sum(Population),FinanceYear))

Kushal_Chawda

Hi,

I think you can choose first option to display data as it is simple to understand also requires only single expression

sum([Cost £])/sum(Population))

Not applicable
Author

Sunny Tand Manish,

Thank you for your quick responses! I think Sunny T, that I agree with the using the second method, re flexilibilty, however I'm not sure that a script per financial year is going to work for each chart. I think it needs to be more flexible.

Manish - I've just tried out your suggestion, I've then selected 2013/14 Financial Year and I get a dot showing the cost per head against one location, but nothing for the others. I think the problem is that in the population table I've only loaded Financial Year and Population, so its not actually linking to a GP practice. But if I link the GP Practice as well as the Financial Year I'll have 2 fields linking to the main dataset and create a sys error......hmmm, might have to think about this one!

Kush141087 - I can't use option one with sum(population) as the population is held in columns called 2012/13 and 2013/14....unless I'm missing the point. Sorry!

sunny_talwar

If you highlight the type of charts you are trying to build, may be we can point you to the right direction.

Not applicable
Author

I'm trying to create a chart (I tried to put a copy in but it didn't work) that shows:

combo chart - bar and line:

y axis - cost

x axis - GP Practices (and other levels of the hierarchy through a cyclical group)

Bar is the cost and the line is the "cost per head" in the given GP Practice

Not applicable
Author

The solution was to create a unique identifier for each Financial Year and GP Practice Code in the Populations datatable and the same in the data source tables too, against each entry. This has solved the problem!