Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mapratt82
New Contributor III

Previous 6 months within Set Analysis

Here is two of my attempts, but I just get null or 0. I have made sure both fields are dates without time stamp, fixed in load.

((Count(distinct{<DDA_External_Tran_Code*={'5'},

DDA_Tran_Date*={>=$(addmonths(today(),-6))}>}DDA_Description_Link)) +

(Count(distinct{<SAV_External_Tran_Code*={'125'},

SAV_Tran_Date*={>=$(addmonths(today(),-6))}>}SAV_Description_Link)))

((Count(distinct{<DDA_External_Tran_Code*={'5'},

DDA_Tran_Date*={">=AddMonths(Today(),-6)"}>}DDA_Description_Link)) +

(Count(distinct{<SAV_External_Tran_Code*={'125'},

SAV_Tran_Date*={">=AddMonths(Today(),-6)"}>}SAV_Description_Link)))

I just want transactions from previous 6 months. I feel confident I could achieve with If statement but would rather keep everything as set analysis if possible. I have probably 15-20 calculations with multiple set analysis that each will need to pull just the previous 6 months worth of data. Unfortunately filtering at the data load isn't going to be a viable option either, please help. Thanks everyone in advance!

1 Solution

Accepted Solutions
antoniotiman
Honored Contributor III

Re: Previous 6 months within Set Analysis

Hi Mark,

try this

((Count(distinct{<DDA_External_Tran_Code={'5'},

DDA_Tran_Date={'>=$(=addmonths(today(),-6))'}>} DDA_Description_Link)) + ....  and so on

Regards,

Antonio

7 Replies
mjtaft2017
Contributor

Re: Previous 6 months within Set Analysis

Updated:  based on discussion below ...

I'm not sure why you have the *= before all of your {'s .  If you want to restrict the transactions to the last 6 months -- then you would not want to intersect with current selections.  Try with just = and not *=  for the dates.

((Count(distinct{<DDA_External_Tran_Code*={'5'},

DDA_Tran_Date={">=AddMonths(Today(),-6)"}>}DDA_Description_Link)) +

(Count(distinct{<SAV_External_Tran_Code*={'125'},

SAV_Tran_Date={">=AddMonths(Today(),-6)"}>}SAV_Description_Link)))

Re: Previous 6 months within Set Analysis

Check here for the use of * before the equal sign

Implicit Set Operators

mjtaft2017
Contributor

Re: Previous 6 months within Set Analysis

I did see that except it didn't make sense for the date part if he only wants transactions from the last 6 months -- the linked document says that intersection adds to existing selection.  How would that restrict to only last 6 months in that case.  Maybe for this part it would be ok (going to add those values to any existing selections)  ...  <DDA_External_Tran_Code*={'5'} and <SAV_External_Tran_Code*={'125'}

but the date portion it would not  (if I understand the purpose of the intersect)

Re: Previous 6 months within Set Analysis

I agree

mapratt82
New Contributor III

Re: Previous 6 months within Set Analysis

I have tried also to remove the * on the dates and it didn't work either. Your and Mary's conversation actually helps in that I couldn't remember why I had used the * on the others just that it was working and I didn't want to mess that up, but now understanding a little I agree it shouldn't be used for what I'm needing on dates.

antoniotiman
Honored Contributor III

Re: Previous 6 months within Set Analysis

Hi Mark,

try this

((Count(distinct{<DDA_External_Tran_Code={'5'},

DDA_Tran_Date={'>=$(=addmonths(today(),-6))'}>} DDA_Description_Link)) + ....  and so on

Regards,

Antonio

mapratt82
New Contributor III

Re: Previous 6 months within Set Analysis

Just want to thank everyone for their help! Thank You!

Community Browser