Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
maybe this:
=IF ( [TDATE] >= $(varDateImplemetation),
COUNT( {$ < [TAMT] = {">=5000"}, [TTC] = {'A','B','C','D'} >} TCODE ),
COUNT( {$ < [TTC] = {'A','B','C','D'} >} TCODE )
)
Hi Andrea,
It still returns false statement. But thanks anyway!
Whats the format of TDATE field?
try if ( Date(Date#([TDATE], 'MM/DD/YYYY'),'MM/DD/YYYY')>= $(varDateImplementation),.....
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
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 //
can you provide a sample data in xl?