Calculated Condition in Treemapin Qliksense

[niha]

Hi,

I have a Treemap that shows top 20 task hours.  Here Dimension is Region, Task and measure is (sum({<FM={'Medical Advisor'}>}Q1hours)+sum({<FM={'Medical Advisor'}>}Q2hours)+sum({<FM={'Medical Advisor'}>}Q3hours)+sum({<FM={'Medical Advisor'}>}Q4hours)). 

Below each category of Task show s number of hours. My requirements is to show the values in percentage of Total hours.  That means If  Sl_intercation is 17155 , then it should show me 17155/ Total MSL hours = say 15 % . So All values should be Percentage and logic for each task is individual MSL hours / Total MSL hours.  For Example the logic is 

=(sum({<FM ={'MSL'}, [Task Name] = {'EExternal SL Engagment - SL interaction'}>}Q1hours)+
sum({< FM ={'MSL'},[Task Name] = {'EExternal SL Engagment - SL interaction'}>}Q2hours)+
sum({<FM ={'MSL'},[Task Name] = {'EExternal SL Engagment - SL interaction'}>}Q3hours)+sum({<FM ={'MSL'},[Task Name] = {'EExternal SL Engagment - SL interaction'}>}Q4hours)

)/(sum({<FM ={'MSL'}>}Q1hours)+sum({< FM ={'MSL'}>}Q2hours)+sum({<FM ={'MSL'}>}Q3hours)+ sum({<FM ={'MSL'}>}Q4hours))

How this will be applied in this situation.

[JordyWegman]

Hi Niha,

Try this, you need the TOTAL because it shouldn't calculate the values in the dimension, but over all dimensions.

=(sum( TOTAL {<FM ={'MSL'}, [Task Name] = {'EExternal SL Engagment - SL interaction'}>}Q1hours)+
sum( TOTAL {< FM ={'MSL'},[Task Name] = {'EExternal SL Engagment - SL interaction'}>}Q2hours)+
sum( TOTAL {<FM ={'MSL'},[Task Name] = {'EExternal SL Engagment - SL interaction'}>}Q3hours)+
sum( TOTAL {<FM ={'MSL'},[Task Name] = {'EExternal SL Engagment - SL interaction'}>}Q4hours)

)/(sum({<FM ={'MSL'}>}Q1hours)+sum({< FM ={'MSL'}>}Q2hours)+sum({<FM ={'MSL'}>}Q3hours)+ sum({<FM ={'MSL'}>}Q4hours))

Jordy

Climber

[niha]

Thanks Jordy. I tried this but not getting my desired results. The below chart show top 20 task values. I want each task values  as percentage of total. for examples . What I did I tool dimension as region, second dimension as task and limited it to top 20 and my measure is sum of all quarters hours. say , sum(Q1+Q2+Q3+Q4) hours . Now I want to show the values of each task as percentage of total. That means for Travel hours for NA, it should be 13390/Sum(Q1+Q2+Q3+Q4)= say 0.16 which is 16 % . So what ever values are coming is chart is correct but only thing is I have to covert it to percentage of total. How and where to put the logic.

[JordyWegman]

I'll get back to this problem, but for now, try to create this in a table. Follow these steps:

1. Create a table
2. Create your current measures
3. Create parts of the measure you want in different columns
4. Try to combine these measures into one so you can create your answer

This is easier than an object like the treemap.

Jordy

Climber

[niha]

Thanks. I will do that. Manger wants to see in percentage as all previous sheet are with  areas and bar chart (stack one with percentage). I will try and get back to you.

[niha]

Hello Jody,

I tried your suggestion . here is the table . The logic for total MSL hours (Top 20)is

(sum({<FM={'MSL'}>}Q1hours)+sum({<FM={'MSL'}>}Q2hours)+sum({<FM={'MSL'}>}Q3hours)+sum({<FM={'MSL'}>}Q4hours))

Now I want another column that will show how many percentage it is with totals. The Logic is 

=(sum({<FM ={'MSL'}, [Task Name] = {' Travel'}>}Q1hours)+sum({< FM ={'MSL'},[Task Name] = {'Travel'}>}Q2hours)+
sum({<FM ={'MSL'},[Task Name] = {'Travel'}>}Q3hours)+sum({<FM ={'MSL'},[Task Name] = {'Travel'}>}Q4hours)

)/
(sum({<FM ={'MSL'}>}Q1hours)+sum({< FM ={'MSL'}>}Q2hours)+sum({<FM ={'MSL'}>}Q3hours)+ sum({<FM ={'MSL'}>}Q4hours)).

This logic is working for me for another sheet of barchart. It will give a decimal values , say 0.13 and that means 13%. 

This logic works for single task named on logic. But I want something where it will evaluate all the top 20 task listed.

That means , In the above equation Taskname = {?????} should be something that should pull all task name listed.