Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
samuel_lin
Contributor

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

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

Tags (1)
11 Replies
MVP
MVP

Re: 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.

Khan_Mohammed
Honored Contributor II

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

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.

MVP
MVP

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

Can we use Aggr in Script?

Khan_Mohammed
Honored Contributor II

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

I don't know. Never tried my self.

Doesn't hurt to try?

Khan_Mohammed
Honored Contributor II

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

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

samuel_lin
Contributor

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

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
Contributor

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

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
Valued Contributor

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

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
Honored Contributor III

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

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)
cheers Lech
When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution.
Please LIKE threads if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem.