11 Replies Latest reply: Jun 16, 2011 12:08 PM by Bikash Debnath

# Date calculation

Hello Everyone,

I have four fields called create date(of a product) and load date(for a product), fiscal year & fiscal month. The requirement is when an User clicks a fiscal year and a fiscal month, then it should count all the products that are in the pending STAGE on that month and that fiscal year. This result is in concatenation with

the count of all the new products that are created after the selected month till today(current day). Load date is used for counting the products that are in Pending stage on the selected month & fiscal year.

For example. Say user selects June 2010(fiscal year 2011). Then count all pending stage products in June 2010. And then count all new products created after the selected month, i.e from July 2010 till current day.

Thanks & Regards,

Bikash

• ###### Date calculation

Hi Bikash,

I started reading through the description but got a bit lost on the way.. more of a visual person when it comes to solving QlikView problems. If you have an example illustrating the issue and explaining what you'd like to see then please add that to the thread

• ###### Date calculation

Hello Jsn,

Here i tried to explain my problem.

Date range is from 1/1/1985 to 3/29/2015

for Created_Date

Date range is from 4/6/2007 to 4/27/2011

And there is a field called Items, which has item numbers. Now these items are in different stages of production. Some is in pending, some is in Design stage etc.

Now when an user selects a particlular month in a fiscal year, then the expression should calculate how many prodcuts/item are in pending stage as of that selected month. and also how many products are been cretaed after that particular selected month.

When count of the item in pending is being calculated, then Load_Date is used as per the requirement.

For example. Say user selects June 2010(fiscal year 2011). Then count all pending stage products in June 2010. And then count all new products created after the selected month, i.e from July 2010 till current day.

Then these two counts should be added together to give the total count.

Hope I was to explain,

Regards,

Bikash

• ###### Date calculation

Please provide us with a Sample data and also mention from which Datefield  u have created the Fiscal Month and Fiscal Year.

• ###### Date calculation

Hi Erika,

how do i add my sample code/apps to this discussion. My apps is around 36MB file.

Thanks & regards,

Bikash

• ###### Date calculation

Hi Everyone,

Thanks for all of your valuable suggestions,

For the time being can someone please answet this,

as you know I have this load_date and created_date. So i want to orient created_date according to load_date.

load_date follows the fiscal year(1st April - 31st March). My data has a weekly refresh on sundays.

So how do i translate this created_date according to the load_date.

Thanks a lot

with warm regards

Bikash

• ###### Re: Date calculation

For example, on Sunday #1, you load the following

Item, Status, Date

123, Pending, 22 May 2011

567, Inventory, 22 May 2011

......................................................................................

On the next Sunday, you load the following:

Item, Status, Date

123, Finished, 29 May 2011

567, Sold, 29 May 2011

......................................................................................

On the next Sunday, you load the following:

Item, Status, Date

123, Inventory, 5 Jun  2011

891, Pending, 5 Jun  2011

If this is your case, the only way I have accomplished this is in the source database itself, because the retention and stamping of the records takes time, and the status codes tend to change more than once in a given week, so the status ends up being by day, by item at a minimum.  If you are dealing with a production line, it can be down to the second.

• ###### Re: Date calculation

Hello Everyone,

Here I have attached an excel with a sample data set. In which I have four fields Load Date, Created Date (when the item was created), Stage of the production and Item number.

My requirement is(if you could please refer to the excel file ) is

Say the user selects the Month-July Year-2010, then I need the count of all the Items in Pending stage in this month.

And the count of all the new items been created after July 2010. Which means from August 2010 till Today. And the summation of this two.

From the sample data set the result should be the following

A. Count of pending items in July 2010= 3. The item numbers are

B. Count of all newly created items after July 2010= 7

C. Total Count=10.

The highlighted in red are the count as in A. And the highlighted in blue are the count as in B.

Thanking you beforehand,

With regards,

Bikash

• ###### Re: Date calculation

I Probably read a little more into this but i think you want to use the intervalmatch functionality to allow the type of date selection you are talking about.

I'm attaching an example. So I get my data, in my case I called it orders. I use the order date as the beginning of the range and then the shipdate as the end of the range. In the case of Pending items I use today's date as the "shipDate" . Next I build up my date table. Finally I create a link table using the iintervalMatch functionality.

You will notice that I leave a synthetic table in the data model, this is probably the only case where I would leave a synthetic table because it is predictable and the possible values are relatively low.

• ###### Date calculation

Bikash,

Please try to explain your data and requirements in terms of tables, how they are related and what type of business process you are trying to describe, I'm having a hard time understanding how load_date and create_date are related or any other details about your data.

Was the example I posted helpful at all? If not, please explain why.

I'm not sure what the attachment limitations are but if you click the "use advanced editor" link when posting a reply the upload box will appear.

Best of Luck

Chris

• ###### Re: Date calculation

I must admit I am still a little fuzzy on your requirements but that is most likely not understanding your business rather than the data. I think what you have here is a relatively simple example of combining mutiple events into the same calendar. the easiest example I can think of with this is presenting Orders and Shipments, you want to select just the month and year and see a summary of all shipments and orders for the month. In your case you are looking for items that were pending and items that were created. If you look at the sample I am attaching you will see that I concatenate your pending items and your created items using different dates to define the date range for which each one will qualify. I then use "intervalmatch" to associate each row with the calendar. You'll notice that a synthetic table has been created, you can take steps to remove it but for this example it was not nessesary

• ###### Re: Date calculation

Hi, chriscammers

Thanks a lot for the sample example.

I have a question. I see that you have created a master calendar, but in my case Load Date is being used for the Fiscal Calendar, so is there any way that I can use the Load Date, instead of creating a new calendar.

Could you please also suggest, how do I load another 25 fields which has to be used for filtering the data. Fields like Tier, End Customer, Product Family, Regions etc. What do I need to modify in the existing code that you have sent.

I have a fact table called Opportunity, which has these other fields along with ITEM Name, Created Date, Load Date.

Thanks,

Bikash