-
Re: Create a customized dimension for customers who purchased in 2015 but not 2016?
Manish Kachhia Dec 15, 2016 12:56 PM (in response to Samuel Lin)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.
-
Re: Create a customized dimension for customers who purchased in 2015 but not 2016?
Samuel Lin Dec 15, 2016 1:15 PM (in response to Manish Kachhia )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,
-
-
Re: Create a customized dimension for customers who purchased in 2015 but not 2016?
Shahbaz Khan Mohammed Dec 15, 2016 12:59 PM (in response to Samuel Lin)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.
-
Re: Create a customized dimension for customers who purchased in 2015 but not 2016?
Manish Kachhia Dec 15, 2016 1:02 PM (in response to Shahbaz Khan Mohammed)Can we use Aggr in Script?
-
Re: Create a customized dimension for customers who purchased in 2015 but not 2016?
Shahbaz Khan Mohammed Dec 15, 2016 1:05 PM (in response to Manish Kachhia )I don't know. Never tried my self.
Doesn't hurt to try?
-
Re: Create a customized dimension for customers who purchased in 2015 but not 2016?
Shahbaz Khan Mohammed Dec 15, 2016 1:08 PM (in response to Shahbaz Khan Mohammed)Ok, I've tried and it doesn't seem to work for me.
-
Re: Create a customized dimension for customers who purchased in 2015 but not 2016?
Samuel Lin Dec 15, 2016 1:10 PM (in response to Shahbaz Khan Mohammed)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.
-
-
-
-
-
Re: Create a customized dimension for customers who purchased in 2015 but not 2016?
Sébastien Fatoux Dec 15, 2016 5:34 PM (in response to Samuel Lin)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)-
Re: Create a customized dimension for customers who purchased in 2015 but not 2016?
Sébastien Fatoux Jan 6, 2017 6:17 AM (in response to Sébastien Fatoux )Bonjour Samuel,
Si une de ces réponse est correcte, veuillez mettre cette question comme résolue pour aider les autres membres de la communauté qui auraient le même problème que vous.
Merci
-
Re: Create a customized dimension for customers who purchased in 2015 but not 2016?
Samuel Lin Jan 9, 2017 7:03 PM (in response to Sébastien Fatoux )Hi Sebastien,
your previous response was correct and worked, I am conducting some tests and will mark your answer as correct soon. Thank you for the reminder!
Thank you everyone for your help, Happy New Year!
-
- In a set analysis, the right part of a selector must be a field, you cannot use function :
-
Re: Create a customized dimension for customers who purchased in 2015 but not 2016?
Lech Miszkiewicz Dec 15, 2016 7:33 PM (in response to Samuel Lin)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:
- load all distinct customers
- Left join only those who did transaction in 2015 and flag them
- Left join only those who did transaction in 2016 and flag them
- 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.
- 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)