Using If Statement with Specific Date as Condition

[Anonymous]

Hi All,

I'm new to Qlik Sense and I'd need help on the following measure. I'm trying to do the following :

1) For transaction with date / [TDATE] equal to or after 1st July 2018, count TCODE with [TAMT] equal to or more than 5000, and, of [TTC] equal to A or  B or  C or  D

2) For transaction with date / [TDATE]  before 1st July 2018, count TCODE with  [TTC] equal to A or  B or  C or  D

The data would look like something like this :

TCODE	TTC	TDATE	TAMT
AWSE	A	7/1/2018	5000
DVFFB	B	2/1/2018	4800
SVSVF	C	6/30/2018	400
CVSFFW	D	7/1/2018	0
AWSE	E	2/1/2018	60
DVFFB	A	6/29/2018	956
SVSVF	C	6/30/2018	3456
CVSFFW	C	5/1/2018	90000
SVSVF	D	3/4/2018	0

I created this variable for comparison :

varDateImplemetation = Date(Date#('7/1/2018', 'MM/DD/YYYY'),'MM/DD/YYYY')

And the following measure for a graph :

IF ([TDATE]>=varDateImplemetation,

     COUNT({$<[TAMT]={'>=5000'},[TTC]={'A','B','C','D'}>} TCODE),

     COUNT({$<[TTC]={'A','B','C','D}>} TCODE))

However the formula always return a false statement. I think something is wrong with the date comparison, but can someone point out where it may be wrong? I've double checked my dates by referring  Why don’t my dates work?and it looks good so far. But I still can't make it work.

Please help. Thanks!

[agigliotti]

maybe this:

=IF ( [TDATE] >= $(varDateImplemetation),

     COUNT( {$ < [TAMT] = {">=5000"}, [TTC] = {'A','B','C','D'} >} TCODE ),

     COUNT( {$ < [TTC] = {'A','B','C','D'} >} TCODE )

)

[Anonymous]

Hi Andrea,

It still returns false statement. But thanks anyway!

[dplr-rn]

Whats the format of TDATE field?

try if ( Date(Date#([TDATE], 'MM/DD/YYYY'),'MM/DD/YYYY')>= $(varDateImplementation),.....

[MK9885]

Maybe

First create 2 variables

vNum = 7

vTAMT = 5000

count  ({<[Year] = {">=$(=Max(Year))"},Num_Month= {">=$(vNum)"} ,

[TAMT]= {">=$(vTAMT)"} , [TTC] = {A,B,C,D} >} TCODE )

You should have Num_Month field in either your Calendar or Fact

num(month(YOURDATEFIELD)) as Num_Month <<<< in your script

Do the same for TTC

count  ({<[Year] = {">=$(=Max(Year))"},Num_Month= {"<=$(vNum)"} ,

[TAMT]= {">=$(vTAMT)"} , [TTC] = {A,B,C,D} >} TCODE )

to combine this into 1 expression...

if(getselectedcount(Year)=0,

count  ({<[Year] = {">=$(=Max(Year))"},Num_Month= {">=$(vNum)"} ,

[TAMT]= {">=$(vTAMT)"} , [TTC] = {A,B,C,D} >} TCODE ) ,

count  ({<[Year] = {">=$(=Max(Year))"},Num_Month= {"<=$(vNum)"} ,

[TAMT]= {">=$(vTAMT)"} , [TTC] = {A,B,C,D} >} TCODE ))

I hope this is correct... lol

[Anonymous]

Hi Dilip,

TDATE format is MM/DD/YYYY. I tried your method too before but still return false statement. Thanks anyway!

Hi Shahbaz,

Still didn;t work too.. I tried simplifying the measure and creating variables in my script but still didn't, sigh. But thanks anyway!

In the end, I used this cheap way and it works   (over time, I'll try to enhance the measure):

IF((MixMatch([TDATE.autoCalendar.YearMonth],'2017-Jan','2017-Feb','2017-Mar','2017-Apr','2017-May','2017-Jun','2017-Jul','2017-Aug','2017-Sep','2017-Oct','2017-Nov','2017-Dec','2018-Jan','2018-Feb','2018-Mar','2018-Apr','2018-May','2018-Jun')),

(COUNT({$<[TTC]={'A','B',C','D'}>}TCODE)),(COUNT({$<[TAMT]={'>=5000'},[TTC]={'A','B',C','D'}>}TCODE)))

*Note : The data starts from Jan 2017

Thanks guys //

[MK9885]

can you provide a sample data in xl?