Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a table with a date dimension which shows me the cash receipt date. To compare the first 21 days of cash receipt each month I need to build a date expression.
example date format is
CashDate:
2/18/2017
2/28/2017
2/22/2017
2/1/2017
2/5/2017
expression:
=Day(Date([Cash App Date]))
18
28
22
1
5
now I want to see only the days <21
so the result should be
18
1
5
Which expression do I need to use?
Thanks a lot.
you can use
if(Day(Date([Cash App Date]))<21, Day(Date([Cash App Date])),'')
you can use
if(Day(Date([Cash App Date]))<21, Day(Date([Cash App Date])),'')
on the top of you can use
only(if(Day(Date([Cash App Date]))<21, Day(Date([Cash App Date]))))
Most of the part, you can do it at the script level itself.
result:
SCRIPT:
temp:
Load *,
Day(Date(CashDate,'MM/DD/YYYY')) as CashDay;
Load * Inline [
CashDate,Cash
2/18/2017,10
2/28/2017,20
2/22/2017,15
2/1/2017,30
2/5/2017,45
];
NoConcatenate
ReduceData:
Load *
Resident temp
where CashDay<=21;
drop table temp;
drop field CashDay from ReduceData;
PSA for the QVW.
this code works perfectly fine in Qlik sense too.
When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others
Dear all, thank you very much for your support. The correct answer which worked for me is if(Day(Date([Cash App Date]))<21, Day(Date([Cash App Date])),''). Thanks a lot
pls close the thread by marking the appropriate one as correct answer.