Using Set Analysis to obtain net movement in a date range.

[#1]

Hi,

I'm trying to use the Set analysis function to obtain the net movement in a selected date range.

The date range is provided by two calander objects.

The records are set up in a "change table" - so each time a record is changed it is date stamped in the table and a new line added.

Consequently I need to get the opening position at the start of the date range and then get the closing positon then take to two away from each other to get the net movement.

for example: Amount of First record within date range - Amount of Last Record in Date Range = net movement.

using the set function I can get the date of the first record and the date of the last record I just can't seem to work out how to get the amount successfully. Here is what I am using to get the last date.

Max({<BTCreateDateTime = {'>=$(=BTCompareStartFormated) <=$(=BTCompareEndFormated)'}>} BTCreateDateTime)

Thanks for your help.

[#2]

Sorry here it is uncropped.

THanks heaps again.

CUST	ORDER No	DATE	VALUE	MAX RESULT	WANT
AB	ORDER 1	2/02/2010	$1,000
AB	ORDER 1	3/02/2010	$1,200
AB	ORDER 1	5/02/2010	$1,100		$1,100
AB	ORDER 2	2/02/2010	$500
AB	ORDER 2	6/02/2010	$700	$700	$700
BCD	ORDER 1	1/02/2010	$400		$400

[mike_garcia]

Hi, sorry I did not get back to you quickly. Did you already solved your problem? If not, Can you post your qvw file? I may be able to check it out or get it to work once I see the object and data model.

Mike.

[#4]

Hi, this is Dave posting on behalf of Steve (slittleford). Check the file attached to this post.

Cheers,

Dave

[mike_garcia]

Hi,

The problem is with the format of the dates. In the BTCreateDateTime Field, you have the date as a concatenation of YYYYMMDD, but QlikView is not interpreting that number as a date, to QV it is just a number. In fact, if you create a list box with that field and assign the number a Date format, for example MM/DD/YYYY, it will give you unexpected results.

So my suggestion is, convert the date variables to numbers that are comparable with the BTCreateDateTime Field. To do that, set the following variables to:

BTCompareStartFormated:

=num(year(BTCompareStartDate)&num(month(BTCompareStartDate),'00')&num(day(BTCompareStartDate),'00'))

BTCompareEndFormated:

=num(year(BTCompareEndDate)&num(month(BTCompareEndDate),'00')&num(day(BTCompareEndDate),'00'))

That should work.

Cheers.

Mike.

[#6]

Hi Mike,

I tryed the above, but it does not seem to work.

Min and max dates now return values out side of the BTCompare start and end boundaries. The date before the BTCOmpareStart now returns nothing and the Before $ now returns nothing. Is there something else I should have done other than modify the variables?

I knwo it is a lot to ask but can you modify my sample and post it back to me? or e-mail it?

Steve,Littleford@totalexact.com.au

Regards

Steve

Steve

[#7]

OK - so it's not a bad as I thought somehow the variables changes to numbers i.e. 40125.

I must have left the "=" off or something.

I have now made the change correctly however it is now behaving just as it was before - i.e. same as the data in the post above.

MIke, thanks again for your help on this - while not yet solved we are moving in the right direction. One more step and i'll be able to buidl the interface.

Regards

Steve

[#8]

Hello,
maybe you can use some of this:
I have a table
 Item // something sold
 Date // date of a sale
 OrderValue // value of the sale
 TotalOrder // counting sum of all sale values of the item // I assume that this value goes UP
Your question equates to:
 geven a Start and an End // both expression via calendar item
 how much has TotalOrder increased between Start and End for each Item
Solution
 straight table
 Dimension: "Item" // since we want to know the increase for each item
 Expression: Max(total <Item> if(Date<=$(#End),[TotalOrder])) - Max(total <Item> if(Date<=$(#Start),[TotalOrder]))
Note that is TotalOrder (your Gross) can go UP and DOWN, then the formula becomes more difficult: Does it?
Greetings,
Piet

[#9]

Just to complete my last post; if the TotalOrder (your Gross) can also go down in time,
then you can use an expression like this
Sum({$<OrderID={$(=Max(total <Item> if(Date<=$(#End),OrderID)))}>} [TotalOrder]) -
Sum({$<OrderID={$(=Max(total <Item> if(Date<=$(#Start),OrderID)))}>} [TotalOrder])
Where OrderID is a unique incrementing counter for each sales record (at least unique per item).
I tried the same via map on the date, but for some reason that never gives a result
Sum({$<[Date]={$(=Max(total <Item> if([Date]<=$(#End),[Date])))}>} [TotalOrder]) -
Sum({$<[Date]={$(=Max(total <Item> if([Date]<=$(#Start),[Date])))}>} [TotalOrder])

Maybe someone knows why? (I assume that converting the Date to a string would make the mapping OK)
Piet</body>