topic Re: Seperate dates into Jan-Jun and Jul-Dec in QlikView

Seperate dates into Jan-Jun and Jul-Dec

qlikmpate0 — Thu, 18 Jun 2015 13:50:50 GMT

I'm trying to separate requisitions using the creation_date into two intervals, first half of the year and the second half.

I'm getting abit muddled can someone help.

I'm thinking of using flags to determine whether the date falls in the first half or second half of the year, but my coding skills are not very good.

thanks in advance

regards

Re: Seperate dates into Jan-Jun and Jul-Dec

sunny_talwar — Thu, 18 Jun 2015 13:54:41 GMT

You can do this in your calendar script

[MasterCalendar]:

LOAD Date,

If(Month(Date) <= 6, 1, 2) as HalfYear,

...

Resident xyz;

Re: Seperate dates into Jan-Jun and Jul-Dec

qlikmpate0 — Thu, 18 Jun 2015 14:05:03 GMT

Can you explain what the 6,1,2 means?

Re: Seperate dates into Jan-Jun and Jul-Dec

sunny_talwar — Thu, 18 Jun 2015 14:11:27 GMT

Month(Date) will give you Jan, Feb, Mar and so on... which is also a dual field meaning that Jan = 1, Feb = 2, Mar = 3 and so on. So when Month(Date) <= 6, it falls under the bucket of Jan, Feb, Mar, Apr, May, Jun which is the 1st half year (1) and the rest of the months are other half (2)

Does that make sense?

Best,

Sunny

Re: Seperate dates into Jan-Jun and Jul-Dec

qlikmpate0 — Thu, 18 Jun 2015 14:40:21 GMT

Yes makes sense but then my results are showing a value of 1 when I was expecting 6 for the shown dates?

Re: Seperate dates into Jan-Jun and Jul-Dec

sunny_talwar — Thu, 18 Jun 2015 14:42:07 GMT

Why are you expecting to see 6? I might have mis-understood your problem. Can you reexplain please?

Re: Seperate dates into Jan-Jun and Jul-Dec

qlikmpate0 — Thu, 18 Jun 2015 14:50:27 GMT

becuase my req date is in the month Jun and the half year value would be 6 if Jun, 1 if Jan 3 if Mar yes?

What I'm strying to do is separate all the requistion dates into two seperate buckets so I can have a selector one for jan-Jun and the other Jul-Dec.

Apologies if I haven't made myself clear.

Re: Seperate dates into Jan-Jun and Jul-Dec

sunny_talwar — Thu, 18 Jun 2015 14:53:18 GMT

What is your date format? DD/MM/YYYY or MM/DD/YYYY? because if its DD/MM/YYYY all your dates fall in 1st half of the year and is displaying the 1 for half year correctly.

HalfYear should be 2 buckets 1 and 2, 1 will include Jan-Jun and 2 will include Jul-Dec.

Re: Seperate dates into Jan-Jun and Jul-Dec

jpenuliar — Thu, 18 Jun 2015 14:56:57 GMT

See if you can adapt the same trick from below:

Apply Map

OP's requirement is to split the year into quarters, solution was ApplyMap()

Re: Seperate dates into Jan-Jun and Jul-Dec

jpenuliar — Thu, 18 Jun 2015 14:57:50 GMT

excerpt:

replace your month names with numbers in your mapping table:

Month_Quarter_Mapping:

Mapping Load * Inline

[

1,Q1

2,Q1

3,Q1

4,Q1

5,Q1

6,Q2

];