Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
ugurarslan
Creator
Creator

Calculate the the first 21 days of cash receipt to compare by month

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.

1 Solution

Accepted Solutions
zebhashmi
Specialist
Specialist

you can use

if(Day(Date([Cash App Date]))<21, Day(Date([Cash App Date])),'')

View solution in original post

6 Replies
zebhashmi
Specialist
Specialist

you can use

if(Day(Date([Cash App Date]))<21, Day(Date([Cash App Date])),'')

zebhashmi
Specialist
Specialist

on the top of you can use

only(if(Day(Date([Cash App Date]))<21, Day(Date([Cash App Date]))))

krishna_2644
Specialist III
Specialist III

Most of the part, you can do it at the script level itself.

result:

1.PNG

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.

pablolabbe
Luminary Alumni
Luminary Alumni

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

ugurarslan
Creator
Creator
Author

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

krishna_2644
Specialist III
Specialist III

pls close the thread by marking the appropriate one as correct answer.