Create a customized dimension for customers who purchased in 2015 but not 2016?

[samuel_lin]

Hello Qlikers!!

I am trying to create a customized dimension to segment out customers who have purchased in both 2015 and 2016, and 2015 only.

right now, I have

-------------------

Aggr(

  If(aggr(sum({<LineType={'Shipped', 'Returned'}, CategoryID={'>0'}, Year([order date])={'2015'}>} ExtRevenue), MasterContactID)>0 and

  aggr(sum({<LineType={'Shipped', 'Returned'}, CategoryID={'>0'}, Year([order date])={'2016'}>} ExtRevenue), MasterContactID)>0,

    Dual('2015&2016', 1),

  If(aggr(sum({<LineType={'Shipped', 'Returned'}, CategoryID={'>0'}, Year([order date])={'2015'}>} ExtRevenue), MasterContactID)>0 and

  aggr(sum({<LineType={'Shipped', 'Returned'}, CategoryID={'>0'}, Year([order date])={'2016'}>} ExtRevenue), MasterContactID)=0,

  Dual('2015&-2016', 2)

  ))

, MasterContactID)

-----------------

it's not working properly, not sure if it's because I have two aggr() function in each if()?

superly appreciate your help~

Samuel

[MK_QSL]

Year([order date])={'2015'}

You have used this in set analysis.

Create

Year([order date]) as OrderYear in script

and use Year = {'2015'}

in set analysis.

Use the same way for other place also.

[MK9885]

I do not know if your expression is correct or no.

But you cannot have a 'Calculated Dimension' in Qlik Sense (I.E you cannot have an expression). It has to be single field.

Maybe use above 2 expressions in Edit Script and give them a name

Ex:

Aggr(

  If(aggr(sum({<LineType={'Shipped', 'Returned'}, CategoryID={'>0'}, Year([order date])={'2015'}>} ExtRevenue), MasterContactID)>0 and

  aggr(sum({<LineType={'Shipped', 'Returned'}, CategoryID={'>0'}, Year([order date])={'2016'}>} ExtRevenue), MasterContactID)>0,

    Dual('2015&2016', 1),

  If(aggr(sum({<LineType={'Shipped', 'Returned'}, CategoryID={'>0'}, Year([order date])={'2015'}>} ExtRevenue), MasterContactID)>0 and

  aggr(sum({<LineType={'Shipped', 'Returned'}, CategoryID={'>0'}, Year([order date])={'2016'}>} ExtRevenue), MasterContactID)=0,

  Dual('2015&-2016', 2)

  ))

, MasterContactID) as NewDimension

You can do within same load of your source table or do a resident load just to create a new field from this expression and use that field in front end as dimension.

[MK_QSL]

Can we use Aggr in Script?

[MK9885]

I don't know. Never tried my self.

Doesn't hurt to try?

[MK9885]

Ok, I've tried and it doesn't seem to work for me.

[samuel_lin]

Aggr() is not possible with load script, has to be applied in the front end.

I am loading my script right now, adding OrderYear, will see if Manish's suggestion would work.

[samuel_lin]

Hi Manish,

this works. However, my script doesn't capture everyone who have purchased in 2015 and didn't purchased in 2016, because those who have not purchased in 2016, won't have any transactional entries in the database, so

aggr(sum({<LineType={'Shipped', 'Returned'}, CategoryID={'>0'}, OrderDateYear_={'2016'}>} ExtRevenue), MasterContactID)=0

will be FALSE for them, and such statement will only capture those who has a revenue of 0 in 2016.

Any thought?

Thanks,

[sfatoux72]

Hi Samuel,

I found two errors in your expression:

• In a set analysis, the right part of a selector must be a field, you cannot use function :
  • Year([order date])={'2015'} is wrong
  • [order year]={2015}   is correct
  • [order date]={'>=01.01.2015<=31.12.2015'}    is correct
• You cannot test if your ExtRevenue is egal to 0 for 2016, because if you dont have ExtRevenue the result is Null, not 0
  • Just add Len function to correct that  ==>  Len(...) = 0

Try this expression, but use your date format (mine is DD.MM.YYYY):

=Aggr(
If(aggr(sum({<LineType={'Shipped', 'Returned'}, CategoryID={'>0'}, [order date]={'>=01.01.2015<=31.12.2015'}>} ExtRevenue), MasterContactID)>0 and
aggr(sum({<LineType={'Shipped', 'Returned'}, CategoryID={'>0'}, [order date]={'>=01.01.2016<=31.12.2016'}>} ExtRevenue), MasterContactID)>0,
Dual('2015&2016', 1),
If(aggr(sum({<LineType={'Shipped', 'Returned'}, CategoryID={'>0'}, [order date]={'>=01.01.2015<=31.12.2015'}>} ExtRevenue), MasterContactID)>0 and
Len(aggr(sum({<LineType={'Shipped', 'Returned'}, CategoryID={'>0'}, [order date]={'>=01.01.2016<=31.12.2016'}>} ExtRevenue), MasterContactID))=0,
Dual('2015&-2016', 2)
))
, MasterContactID)

[Lech_Miszkiewicz]

hi everyone.

I know sometimes expressions /calculated dimesnions are much better as you can dynamicly change your selections for example to compare also those customers who bought something in January 2015 and January 2016 so you can drill your selection down to one month....etc.

However as we are talking now about customers who bought in 2015 and 2016 or only in 2015 why dont we create flags or even separate feild for them?

I would do following in script:

1. load all distinct customers
2. Left join only those who did transaction in 2015 and flag them
3. Left join only those who did transaction in 2016 and flag them
4. using If statement and flags from point 2 & 3 create anothe flags for those who bought in both years and in one of these years.
5. Based on those flags you can then create separate dimensions for new customers (did not buy in prior year but but in current), customers who "left"(bought in prior year but not in current), existing customer (did buy something in both years)