Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Can we use Aggr in Script?
I don't know. Never tried my self.
Doesn't hurt to try?
Ok, I've tried and it doesn't seem to work for me.
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.
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,
Hi Samuel,
I found two errors in your expression:
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: