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

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.

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.

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)

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)