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: 
Anonymous
Not applicable

Nested set analysis (trendline in Whale Chart)

Hello,

I  have a so-called whale chart (see attachment) with the following dimension:

aggr(num(rank((sum({$<Project_status={1}>} Project_income)-sum({$<status={1}>} Project_cost)), 4)), Customer_code)

expression:

sum({$<Project_status={1}>} Project_income)-sum({$<status={1}>} Project_cost

and trendline:


sum(TOTAL{$<Project_status={1}>} Project_income)-sum(TOTAL{$<Dagboek={1}>} Project_cost)

Now I also would like to have a similar trend line but only related to projects that have a positive margin (Project_income > Project_cost) and the Project_status should be 1.

BTW: Each project has a unique id: Project_Id

Any thoughts on how I could create this trend line?

Cheers,
Rudi

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Hi Rudi,

Use following

sum({$<Project_Id = {"=Project_income > Project_cost"},Project_status={1}>}Project_cost)

Else you can can raise a flag in scrip like

if(Project_income > Project_cost,'ProfitableProject','HighCostProject') as ProjectStatus and then use in setanalysis as

sum({<$ProjectStatus={'ProfitableProject'},Project_status={1}>}Project_cost)

Thanks,

View solution in original post

3 Replies
MK_QSL
MVP
MVP

Project_Id = {"=Project_income > Project_cost"}

Add above to all your set analysis.

Anonymous
Not applicable
Author

Hi Rudi,

Use following

sum({$<Project_Id = {"=Project_income > Project_cost"},Project_status={1}>}Project_cost)

Else you can can raise a flag in scrip like

if(Project_income > Project_cost,'ProfitableProject','HighCostProject') as ProjectStatus and then use in setanalysis as

sum({<$ProjectStatus={'ProfitableProject'},Project_status={1}>}Project_cost)

Thanks,

Anonymous
Not applicable
Author

Hi Manish, Pooja, a delayed thank you for your response!

I now got the question to add a trend line that should give the maximum total profit at Customer_code. Which means that I have to sum all aggregated results per customer_code, but exclude the results that are negative at customer_code.

Maybe a bold question and claiming too much, but do you have thoughts on how I could construct this nested aggr statement?

Cheers,

Rudi