Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

Re: Percentage of total, per month using set analysis

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]

)

Highlighted
Contributor III
Contributor III

Re: Percentage of total, per month using set analysis

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!

Highlighted

Re: Percentage of total, per month using set analysis

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

Highlighted
Contributor III
Contributor III

Re: Percentage of total, per month using set analysis

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!

Highlighted

Re: Percentage of total, per month using set analysis

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

Highlighted
Contributor III
Contributor III

Re: Percentage of total, per month using set analysis

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