Percent of Total for Dimension, Ignoring Selections

[#1]

Good morning, Community!

In the attached spreadsheet, I've included sample data and some "charts" that present the information in the required manner.

We allocate employee pay across projects every month , and I'm pulling from a table view in our ODS (all I have access to).  It includes the name, project number, dollars allocated, and period.

What I need to be able to do is always present the employee's pay as a percentage of THEIR OWN total for the month, regardless of selections of any of the dimensions, and regardless of how many instances of a dimension I select at a time.

I've tried everything I can think of - set analysis $/1, aggregations, Total functions, etc...and no matter what I do, when I make a selection, the % of total always changes so that it is using the selection value as the denominator.

Most of the time, I'm selecting one project in order to see all the employees paid from that project for the period.  And inevitably, the % of total for each one shows 100%.

Can anyone help me with this?  I've read everything I can find on set analysis, etc...downloaded sample files...and I am just stuck.

Many thanks!

[teiswamsler]

HI Camilla

Here is some to start with, dont have time for more right now

/Teis

[Gysbert_Wassenaar · Accepted solution]

See attached example. Does that answer your question?

[sunny_talwar]

Gysbert you might have forgotten to add the attachment:

[miguelbraga]

Hi Camilla,

Take a look at my solution.

Thanks,

MB

[#6]

Hi, Braga-

Did you mean to attach something?

[Gysbert_Wassenaar]

Actually, I didn't forget. There seems to be a bug that causes attachements to not show up. As soon as I add it again it will show two attachments. So the first one was attached after all. Anyway, it should be visible to all now.

[#8]

Interesting - I can only see attachments if I open qlik community in IE...I use Chrome most of the time and it doesn't like it.

THANK YOU all of you for sending your suggestions.  I'm going to look at them all and will come back and follow up.

[#9]

Gysbert - you have saved me again.

I need to study brackets and curly brackets again...I had used the formulas you included, just with the wrong placement of brackets...not just error, need to really beef up my understanding of the impact of them.

THANK YOU SO MUCH!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

[#10]

Hi, Gysbert!  I've had another issue pop up with this that is once again giving me fits. You had kindly helped me figure out how to do a set analysis/total solution on the above so that I could show each employee's % of THEIR total salary for the month.

I've been given a file that has TWO months worth of data, instead of just one.  And I'll be darned if I can figure out how to ensure that when I select a period, that I am getting % of total for JUST THAT PERIOD, and not both (in other words, most of my  % are being cut in 1/2 when I select one month, instead of showing the % of the employee total just for the selected month). All the other requirements are the same.   I can't seem to attach the excel file again - I just went in and split the data between two dates, but it won't let me upload.  I thought this would be easy once I had your solution.  I have been beating my head against the wall all day.. any suggestions you can provide would be great!

Camilla