topic Re: Percentage of total, per month using set analysis in App Development

Percentage of total, per month using set analysis

drminaker — Mon, 14 May 2018 16:07:19 GMT

Hi,

I’m having an issue getting percentages to add up to 100, by month, when using set analysis to define different conditions.

As always, any help is much appreciated.

For example:

This works exactly as expected and provides monthly totals out of 100%.

sum(

{$<

[Office]={'Toronto', ‘Montreal’,’Vancouver’}

[Hours]

)

sum(Total<Month>

{$<

[Office]={'Toronto', ‘Montreal’,’Vancouver’}

However, if I want to compare hours per month in Toronto, Montreal, and Vancouver against the hours in all the other offices, by excluding those three offices, I can’t get it to work. The percentages are correct, but I cannot get them to display correct per month. I've tried numerous things TOTAL, ALL, AGGR etc.

sum(

{$<

[Office]={'Toronto', ‘Montreal’,’Vancouver’}

[Hours]

)

sum(Total<Month>

{$<

[Office]-={'Toronto', ‘Montreal’,’Vancouver’}

[Hours]

)

Re: Percentage of total, per month using set analysis

sunny_talwar — Mon, 14 May 2018 16:21:46 GMT

Not sure, but may be one of these

sum(

{$<

[Office]={'Toronto', ‘Montreal’,’Vancouver’}

[Hours]

)

sum(Total<Month, Office>

{$<

[Office]-={'Toronto', ‘Montreal’,’Vancouver’}

[Hours]

)

sum(

{$<

[Office]={'Toronto', ‘Montreal’,’Vancouver’}

[Hours]

)

sum(Total<Office>

{$<

[Office]-={'Toronto', ‘Montreal’,’Vancouver’}

[Hours]

)

Re: Percentage of total, per month using set analysis

drminaker — Mon, 14 May 2018 16:43:33 GMT

Thanks for the suggestions, but it still doesn't want to work. I experimented a bit with having two dimensions after the total... I thought that would have been it!

Re: Percentage of total, per month using set analysis

sunny_talwar — Mon, 14 May 2018 16:49:47 GMT

Would you be able to show (in an image may be) what you have and explain what you are looking to get?

Re: Percentage of total, per month using set analysis

drminaker — Mon, 14 May 2018 19:40:56 GMT

Hi Sunny,

I mocked up an example of what I'm trying to do and attached the QVF. There are two % that I'm trying to calculate, one which = 100% in the table and the other that = 44%. I'm trying to get the 44% to equal 100%, when compared month-to-month.

Hopefully this makes sense and thanks for looking!

Re: Percentage of total, per month using set analysis

sunny_talwar — Mon, 14 May 2018 19:46:39 GMT

I broke down your expression into two pieces... I guess that the numerator part should not be a problem, right? Denominator is where you would want the expression result to change? What should it change to?

Re: Percentage of total, per month using set analysis

drminaker — Mon, 14 May 2018 20:29:20 GMT

Hi,

I'm trying to take the sum of hours for Tor, Mon, Van (387) and then divide against the sum of all the other cities, not including Tor, Mon, Van (894). I end up with 44% (17% Toronto, 15% Montreal, 12% Vancouver). Ideally I want these percentages to equal 100% not, 44% and then, distribute the 100% across Month.

So yes, my issue is with the denominator, but I can't figure out exactly what I need to do...