Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Talk to Experts, a LIVE Q&A Webinar. Bring your Qlik Sense Business questions on Aug. 4th. Register
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

IF SUM Customer Flag

Hi 

I am trying to create flag with customers lost/active. I have 4 quarters, if they did not buy in last two quarters (0, 1) I consider them lost. But I want to apply the flag lost for the whole ID despite they had revenue in remaining two quarters (2,3). Now is showing me lost for 0, 1 but active for 2, 3 in case there was revenue but should be considered as lost.

=IF(sum({$<qtr_flag={0,1}>}revenue) <> 0, 'Active','Lost')

Thanks a lot I attached sample 

4 Replies
Highlighted
Partner
Partner

Re: IF SUM Customer Flag

Hi,

What if you try this?

=IF(Aggr(sum({$<qtr_flag={0,1}>}revenue),Customer) <> 0, 'Active','Lost')

Jordy

Climber

Work smarter, not harder
Highlighted
Partner
Partner

Re: IF SUM Customer Flag

I have done it this way in the loading script : 

 

Sales:
Load * INLINE [
ID, Qtr, Revenue
1, 0, 0 
1, 0, 1000
2, 1, 2000
2, 1, 2000
2, 1, 2000
];

SalesTMP:
Load 
RowNo() as rownumber,
*,
If(Qtr = 0, 'Lost','Active') as Flag
Resident Sales;

// Clean UP
Drop Table Sales;
Rename Table SalesTMP to Sales;
Highlighted
Contributor III
Contributor III

Re: IF SUM Customer Flag

Hi Jordy

This might be working, I am just trying to figure out make it work with the flag because its either all Active or Either or Lost.

IF(Aggr(sum({$<qtr_flag={0,1}>}revenue),crm_bp_id) <> 0 AND Aggr(sum({$<qtr_flag={0,1}>}revenue),crm_bp_id) ='', 'Active','Lost')

I attach the excel formula where its working the way I want it to be working in Qlik.

Thanks for any ideas

Jana

Highlighted
Contributor III
Contributor III

Re: IF SUM Customer Flag

Solved it 😁

IF(sum(TOTAL <crm_bp_id >{$<qtr_flag={0,1}>}revenue) <> 0, 'Active','Lost')

Thanks all for the help!  Nice Day

Jana