topic Re: Dynamic Objects on Year and Month in QlikView

Dynamic Objects on Year and Month

nareshthavidishetty — Fri, 20 Nov 2020 18:02:30 GMT

Hi all,

I want to create dynamic calendar,

I have the data like,

DATE(DD/MM/YYYY)

19/04/2007

17/05/2007

31/05/2007

14/06/2007

16/06/2007

17/06/2007

02/07/2007

05/07/2007

I want to create Dynamic calendar on (MM/YYYY) basis.

When I'm trying to do (MM/YYYY) format, it is not working.

PFA of QVW,

Can you please any one help on these...

Re: Dynamic Objects on Year and Month

chaper — Wed, 19 Aug 2015 18:28:15 GMT

Why are you using date# in script??? Use Date(Orders,'MM/YYYY')

Re: Dynamic Objects on Year and Month

nareshthavidishetty — Thu, 20 Aug 2015 04:00:20 GMT

In my case the data is like('DD/MM/YYYY hh:mm:ss'),

if i am doing Date(Orders,'MM/YYYY') it allowing duplicates.

ex:

19/04/2007 00:00:00

19/04/2007 12:03:59,

because of that i am using date# function:date#(Date(Orders,'MM/YYYY'))

Re: Dynamic Objects on Year and Month

ziadm — Thu, 20 Aug 2015 04:44:58 GMT

Try to format your excel sheet column by changing it from Text type to date Type

Click on the excel column and right clik -- format them choose date

Re: Dynamic Objects on Year and Month

jonathandienst — Thu, 20 Aug 2015 04:58:12 GMT

naresh kumar wrote:

In my case the data is like('DD/MM/YYYY hh:mm:ss'),

if i am doing Date(Orders,'MM/YYYY') it allowing duplicates.

ex:

19/04/2007 00:00:00

19/04/2007 12:03:59,

because of that i am using date# function:date#(Date(Orders,'MM/YYYY'))

That does not work - you need to strip the time value and day number from your Order field if you want to show months only:

Date(MonthStart(Orders), 'MM/YYYY')

will not have duplicates.

Re: Dynamic Objects on Year and Month

jonathandienst — Thu, 20 Aug 2015 04:59:24 GMT

See

QlikView Date fields

Why don’t my dates work?

Re: Dynamic Objects on Year and Month

qlikmsg4u — Thu, 20 Aug 2015 06:44:30 GMT

Use this, this will solve your problem

LOAD date(orders) as OrderDate,

date(floor(orders),'MM/YYYY') as Orders1

from source

Re: Dynamic Objects on Year and Month

jonathandienst — Thu, 20 Aug 2015 06:51:27 GMT

That will have 30 identical entries for each month...

Re: Dynamic Objects on Year and Month

qlikmsg4u — Thu, 20 Aug 2015 07:25:04 GMT

Hijontydkpi

No,I did't get that much records.

Re: Dynamic Objects on Year and Month

jonathandienst — Thu, 20 Aug 2015 07:31:25 GMT

Sure you didn't - that is just test data with only a few fact lines - but you are getting duplicates:

If you have facts for 30 days in a month, then you will get 30 entries in Orders1 for that month.

Re: Dynamic Objects on Year and Month

qlikmsg4u — Thu, 20 Aug 2015 07:36:33 GMT

Ahhh

jontydkpi didn't noticed that, thanks for correcting me

Re: Dynamic Objects on Year and Month

chaper — Thu, 20 Aug 2015 12:43:49 GMT

I was late to reply.Got it.