topic Re: Date functionality in QlikView

Date functionality

udaya_kumar — Tue, 13 May 2014 14:25:33 GMT

Hi,

I have two tables date_dim and shops_dim.

shops_dim has shop_dim_id, goods_incoming_date and other fields

date_dim has month_date like jan-12,feb-12,mar-12 like this 24 months

Now i need to find the count of shops which had goods_incoming in latest 3 months.

Like if a shop1 gets goods1 at 23-mar-12,

shop2 gets goods2 at 12-feb-12

shop3 gets goods3 at 11-jan-12

shop4 gets goods4 at 12-jan-12

Now if we count the no. of shops which took goods in latest 3 months (jan,feb,mar-12) should be 4.

so i have to find the no. of shops which took goods based on goods_incoming_date.

I should find the no. of goods_incoming_date present in the latest 3months (jan,feb,mar).

How can i find the no. of shops based on date?

one shop will have one date only.

Re: Date functionality

Tue, 13 May 2014 14:30:56 GMT

Can you attach the example?

Re: Date functionality

Anonymous — Tue, 13 May 2014 14:39:20 GMT

You can create a full calendar, and link it to the shops by the date field.

But in your case, if you have only one record per shop, it may not be needed. Just re-format goods_incoming_date in format 'mmm-yy'. Can be done by cutting off the day part, or by using date(date#()) functions.

Re: Date functionality

udaya_kumar — Tue, 13 May 2014 15:39:47 GMT

Hi,

Thanks for the reply.

Can you tell me how can i create an id for the dates in a separate field?

for example..

I have dates like

1-jan-2012

5-jan-2012

2-feb-2012

30-mar-2012

29-mar-2012

So i want id's for the dates like

Month ID

1-Jan-2012 1

5-Jan-2012 1

2-Feb-2012 2

30-Mar-2012 3

29-Mar-2013 3

ID's should be based on Month-Year.

In a date, if month-year repeats then the ID should be same

like 1-Jan-2012 and 5-Jan-2012

i have common month-year

so both should have ID as 1.

How can i get this ID field in script?

Re: Date functionality

Anonymous — Tue, 13 May 2014 15:48:13 GMT

I guess that this is what you want:

date(date#(goods_incoming_date, 'D-MMM-YYYY'), 'D-MMM-YYYY') as Month // actually it is date not month

num(month(date#(goods_incoming_date, 'D-MMM-YYYY'))) as ID