Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Don't miss the upcoming Q&A with Qlik session on Qlik Application Automation on November 16th! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
peterstalberg
Contributor III
Contributor III

How to split a measure in positive and negative values?

Hi Guys,

I have the following measure which calculate a value based on ESTIMATE_AMOUNT now and previous year. The result can be either positive or negative per C_ID. The measure works fine 🙂

-sum(aggr(sum(ESTIMATE_AMOUNT)-above(Sum(ESTIMATE_AMOUNT)), (C_ID, numeric, ascending)),PERIOD_YEAR,numeric, ascending))))

Now to my issue, I want to show the sum of positive & negative values per year separated and stacked in a bar chart over the dimension PERIOD_YEAR. My approach is to create two separate measures, one each for the positive and negative sum.

To make it handy I have made a variable (v_run_off) of the above measure.

I have tried this (for the positive measure) but it doesn't separate over the year, just returns the total;

if($(v_run_off)>0, $(v_run_off))

Now this but it doesn't give me the desired result;

aggr(sum(aggr(if(($(v_run_off))<0, $(v_run_off),0),C_ID)),PERIOD_YEAR)

Any guidance on how to solve this would be highly appreciated. RGDS//Peter

1 Solution

Accepted Solutions
peterstalberg
Contributor III
Contributor III
Author

Hi Anil,

Thank you for your response and for pointing me in the right direction. Yes, you are correct, I made this overly complicated. The trick was to put the if-statement inside the first aggregation (and skip the other aggr :-)). For negative values, here is the solution that works (with an added number formating). Just flip the < for positive values;

num(-sum(aggr(if(ESTIMATE_AMOUNT>above(ESTIMATE_AMOUNT), (ESTIMATE_AMOUNT-above(ESTIMATE_AMOUNT))), (C_ID,(NUMERIC, ASCENDING)), (PERIOD_YEAR,(NUMERIC, ASCENDING)))),'# ##0')

Thanks and RGDS//Peter

 

View solution in original post

2 Replies
Anil_Babu_Samineni

I am not sure why one more aggregate required? Can't you try this

sum(aggr(if($(v_run_off)<0, $(v_run_off),0),C_ID, PERIOD_YEAR))

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
peterstalberg
Contributor III
Contributor III
Author

Hi Anil,

Thank you for your response and for pointing me in the right direction. Yes, you are correct, I made this overly complicated. The trick was to put the if-statement inside the first aggregation (and skip the other aggr :-)). For negative values, here is the solution that works (with an added number formating). Just flip the < for positive values;

num(-sum(aggr(if(ESTIMATE_AMOUNT>above(ESTIMATE_AMOUNT), (ESTIMATE_AMOUNT-above(ESTIMATE_AMOUNT))), (C_ID,(NUMERIC, ASCENDING)), (PERIOD_YEAR,(NUMERIC, ASCENDING)))),'# ##0')

Thanks and RGDS//Peter

 

View solution in original post