Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Sense
- :
- Calculated Condition in Treemapin Qliksense

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Showing results for

niha

Creator II

2019-12-09
03:40 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Calculated Condition in Treemapin Qliksense

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.

392 Views

5 Replies

JordyWegman

Partner - Master

2019-12-10
04:21 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Work smarter, not harder

370 Views

niha

Creator II

2019-12-10
09:30 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

362 Views

JordyWegman

Partner - Master

2019-12-10
10:00 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

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

This is easier than an object like the treemap.

Jordy

Climber

Work smarter, not harder

358 Views

niha

Creator II

2019-12-10
01:48 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

349 Views

niha

Creator II

2019-12-11
01:45 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

332 Views

Community Browser