Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

11 Replies
Anonymous
Not applicable
Author

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

chriscammers
Partner - Specialist
Partner - Specialist

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.

Not applicable
Author

Hello Jsn,

Thanks for your prompt response.

Here i tried to explain my problem.

for Load_Date

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

Not applicable
Author

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

Not applicable
Author

Hi Erika,

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

Thanks & regards,

Bikash

Not applicable
Author

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

chriscammers
Partner - Specialist
Partner - Specialist

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

Not applicable
Author

When I read your thread, I think you are asking about how to track current and historical items categorized by their production status at the time they were loaded.

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.

Not applicable
Author

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