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: 
drminaker
Contributor III
Contributor III

Percentage of total, per month using set analysis

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]

)


6 Replies
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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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?

Capture.PNG

drminaker
Contributor III
Contributor III
Author

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