topic Re: Need help on Month & Date in QlikView

Need help on Month & Date

Thu, 31 Jul 2014 11:01:09 GMT

I'm new to qlikview and need your help in the below scenario.

I have a date field "Field1" with normal dates. ( say from jan 1 2008 to current date in dd/mm/yyyy format )

Now i want a month filed "Field2" out of this dates .. condition is dates from 26th of every month to 25th of next month should be grouped as a month .. any suggestions will be greatly helpful !

Re: Need help on Month & Date

amit_saini — Thu, 31 Jul 2014 11:05:52 GMT

Venkat,

You can create two Month variable like vMonthStart and vMonthEnd and assign them desired range values.

Thanks,
AS

Re: Need help on Month & Date

MarcoWedel — Thu, 31 Jul 2014 11:10:33 GMT

MonthName(Field1 - 25) as Field2

regards

Marco

Re: Need help on Month & Date

Thu, 31 Jul 2014 11:16:09 GMT

Amit,

As i said , i'm new to qv. Can you please elaborate.

My requirement is , for e.g if a user selects any date between "26 jan" to "25 feb" if should come user accounting month ( filed name) Feb, if he selects accounting month march all dates from 26 feb to 25 march should be considered

Re: Need help on Month & Date

amit_saini — Thu, 31 Jul 2014 11:20:13 GMT

Venkat,

Understand !

Could you please share your source file or application .

Thanks,

Re: Need help on Month & Date

jonathandienst — Thu, 31 Jul 2014 11:23:52 GMT

Use script like this when loading you calendar (adapt field names to your requirements):

Calendar:

LOAD Date,

Month(Date) As CalendarMonth,

Month(If(Day(Date) <= 25, Date, AddMonths(Date, 1))) As FiscalMonth,

...

HTH

Jonathan

Re: Need help on Month & Date

Thu, 31 Jul 2014 11:52:16 GMT

Amit,

Thank you for the help .. Jonathan trick has solved my problem

Re: Need help on Month & Date

Thu, 31 Jul 2014 11:55:09 GMT

Marco,

Results were a bit different that i expected.. i get results from feb 26 to march 25 when i select feb .. what i really want is jan26 to feb 25 when i select feb.. useful suggestion though , can be used for other requirements 😉

Re: Need help on Month & Date

Thu, 31 Jul 2014 11:57:03 GMT

Jonathan,

It worked like a charm .. Thank you very much 🙂

Re: Re: Need help on Month & Date

MarcoWedel — Thu, 31 Jul 2014 23:15:10 GMT

that's a static offset which can be corrected like (without the need for any potentially time consuming conditions):

MonthName(Field1 - 25, 1) as Field2

one sample app:

table1:
LOAD *,
     MonthName(Field1 - 25, 1) as Field2;
LOAD
  Date(Date#('01/01/2008', 'MM/DD/YYYY')+IterNo()-1, 'MM/DD/YYYY') as Field1
AutoGenerate 1
While Date#('01/01/2008', 'MM/DD/YYYY')+IterNo()-1 <= Today();

hope this helps also

regards

Marco

Re: Re: Need help on Month & Date

Fri, 01 Aug 2014 05:44:07 GMT

Thanks a lot 🙂

Re: Need help on Month & Date

MarcoWedel — Fri, 01 Aug 2014 06:50:04 GMT

You're welcome.

please mark as helpful if it helps.

thanks

regards

Marco

Re: Need help on Month & Date

Thu, 27 Nov 2014 02:49:26 GMT

So I want to use this to define an existing Shipping Date range for the same reason but how do I tie the date range file to the shipping calendar?