Percentage of total, per month using set analysis

[drminaker]

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]

)

[sunny_talwar]

Not sure, but may be one of these

sum(

{$<

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

>}

[Hours]

)

/

sum(Total<Month, Office>

{$<

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

>}

[Hours]

)

or

sum(

{$<

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

>}

[Hours]

)

/

sum(Total<Office>

{$<

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

>}

[Hours]

)

[drminaker]

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!

[sunny_talwar]

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

[drminaker]

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!

[sunny_talwar]

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?

[drminaker]

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...