topic Nested set analysis (trendline in Whale Chart) in QlikView

Nested set analysis (trendline in Whale Chart)

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Nested set analysis (trendline in Whale Chart)

MK_QSL — Mon, 07 Nov 2016 08:51:01 GMT

Project_Id = {"=Project_income > Project_cost"}

Add above to all your set analysis.

Re: Nested set analysis (trendline in Whale Chart)

Anonymous — Mon, 07 Nov 2016 10:14:21 GMT

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,

Re: Nested set analysis (trendline in Whale Chart)

Anonymous — Thu, 10 Nov 2016 15:01:16 GMT

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