6 Replies Latest reply: Sep 21, 2018 12:03 PM by Shahbaz Khan Mohammed

# Using If Statement with Specific Date as Condition

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.

• ###### Re: Using If Statement with Specific Date as Condition

maybe this:

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

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

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

)

• ###### Re: Using If Statement with Specific Date as Condition

Hi Andrea,

It still returns false statement. But thanks anyway!

• ###### Re: Using If Statement with Specific Date as Condition

Whats the format of TDATE field?

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

• ###### Re: Using If Statement with Specific Date as Condition

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

• ###### Re: Using If Statement with Specific Date as Condition

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 //

• ###### Re: Using If Statement with Specific Date as Condition

can you provide a sample data in xl?