[ASK] how to change first date and end date of month ?

[#1]

hello..

i have a client who start the first date of month at 17th and finish the last date of month at 16th

is it possible to make QlikView do that?

if it is yes, how to make it possible?

i cant make a load inline since the data is too big

thx..

[#2]

Hi,

My idea, having standard calendar, is to create a mapping table:

- first column: DATE

- second column: MONTH

Creating mapping table you have to check:

- if examined DAY is greater than 17 than MONTH = MONTH(DATE)

- if examined DAY is less than 16 than MONTH = MONTH(NUM(MONTH(DAY))-1)

You have to be careful with the first and last month of the year.

Having mapping table you can use it in any part of the loading script.

Regards,

Janusz Twardziak

[#3]

@Janus

hello

i've tried this on edit script :

if(day(CicilDate)<16,month(CicilDate),month(CicilDate)+1) as month_CAD,

but the month_CAD has month 13th

how to add that month 13th into month 1st and year+1 ?

i confuse at this part

thx...

[jonathandienst · Accepted solution]

Perhaps this is what you need:

if(day(CicilDate)>16, RangeMin(12, month(CicilDate)+1), month(CicilDate)) as month_CAD,
if(day(CicilDate)>16 and month(CicilDate)=12, year(CicilDate)+1, year(CicilDate)) as year_CAD,

Jonathan

[#5]

@Jonathan

hello Jho..

u help me a lot one more time

thx a lot..

🙂

i use :

if(day(CicilDate)>16, RangeMax(12, month(CicilDate)+1), month(CicilDate)) as bulan_CAD,

if(day(CicilDate)>16 and month(CicilDate)=12, year(CicilDate)+1, year(CicilDate)) as tahun_CAD,

i use RangeMax, so when month(CicilDate)+1=13 it backs to month(cicilDate)=1

🙂

btw many thx

🙂