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

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

Mon, 15 Mar 2010 21:53:56 GMT

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.

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

mike_garcia — Tue, 16 Mar 2010 00:46:25 GMT

I see you are using single quotes where I am almost certain there should be double quotes.

Other trhan that, everything seems fine and should work if the dates are properly formatted, which I guess they are by the name of the fields.

Good Luck.

Mike.

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

Tue, 16 Mar 2010 00:59:08 GMT

Thanks Mike,

Yes I changed it to double quotes and yes the dates are working fine, however what is happening is I'm still just getting a total of all of the records within the variable boundary rather than the amount on the last (max) record. Even if I use distinct I get a total of each change in amount.

All I want is the first record amount and the last record amount within the boundary

Thanks

Steve

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

mike_garcia — Tue, 16 Mar 2010 03:07:29 GMT

Oh I get it!

What is the formula you are using to get the Net Amount? Are you storing the max date and min date into variables?

I would do that and then use those variables in the set analysis. Somethinkg like:

sum({$<BTCreateDateTime = {$(vMaxDate)}>} Amount) - sum({$<BTCreateDateTime = {$(vMinDate)}>} Amount)

Hope this helps.

Mike.

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

Tue, 16 Mar 2010 03:29:38 GMT

Can I really put the value of a set in a variable? I've tried it and it just returns a constant value.

Here is what I am trying just to get the value on the highest dated record in the range - but it just returns the total value of all records in the range not the value of the max record. The middle bit i.e. the return of the Max Date works fine - I just can't get the amount of the record as well.

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

Thanks heaps for giving this a go asnd helping me

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

mike_garcia — Tue, 16 Mar 2010 04:41:22 GMT

Ok, I think you are using set analysis a little different here. To get the value of the record where the date equals the max date that is within the range provided, use the following expression:

Sum({$<BTCreateDateTime = {$(=Max({$<BTCreateDateTime ={">=$(=BTCompareStartFormated) <=$(=BTCompareEndFormated)"}>}BTCreateDateTime))}>} BTGross)

That should work.

Mike.

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

Tue, 16 Mar 2010 05:54:00 GMT

HI Mike,

Ok, i feel like im being difficult now. The above works if the BTCreateDateTime actually equals the Max Date or Min Date - trouble is that because they are record change dates the records do not necessarily exist on every day. I might only have 2 days in a given week where a change was made.

For example

date range 1/2/2010 to 7/2/2010

Record 1 change date = 2/2/2010 value $1000

Record 2 change date = 4/2/2010 value $1100

Record 3 Change date 5/2/2010 value $1200

I need to return a net change of $200

Further more if I change my date range to the 3/2/2010 to 7/2/2010 the nthe net change would be $100

Tell me if I'm just getting annoying 🙂

Regards

Steve

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

Tue, 16 Mar 2010 07:06:26 GMT

My apologies. your set seems to work differently to how I thought. What it is doing is picking up only the first instance regardless of the grouping. I am diplaying by order and the set only works for the MAX date across all the orders.

If we extend the example

date range 1/2/2010 to 7/2/2010

Order 1 Record 1 change date = 2/2/2010 value $1000

Order 1 Record 2 change date = 4/2/2010 value $1100

Order 2 Record 3 Change date 5/2/2010 value $1200

The result I want is $100 for Order 1, $1200 for Order 2

The current Set statement just returns $1200

Regards

Steve

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

mike_garcia — Tue, 16 Mar 2010 13:55:39 GMT

Hello,

Yes, the expression gives you only the first value you need. To get the net amount, now you need to make the following:

<pre>Sum({$<BTCreateDateTime = {$(=Max({$<BTCreateDateTime =
{">=$(=BTCompareStartFormated) <=$(=BTCompareEndFormated)"}>}
BTCreateDateTime))}>} BTGross)
-
Sum({$<BTCreateDateTime = {$(=Min({$<BTCreateDateTime =
{">=$(=BTCompareStartFormated) <=$(=BTCompareEndFormated)"}>}
BTCreateDateTime))}>} BTGross)

And no, you are not being annoying. That is what QlikCommunity is for: to help solve problems and learn new stuff. I encourage you to ask whatever questions you have. That way, you walk the road of continuous learning and allow others to learn through your experiences.

Let us know if the code worked.
Cheers.
Mike.

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

Tue, 16 Mar 2010 20:36:45 GMT

Hi Mike,

Yes I understood the netting off. What I meant was that it seems to be ignoring the dimensions, so I am only returning one value across the pivot chart based on the selected dates. So I am not getting the highest value per order even though I have a dimension for that.

I would upload my sheet but the data set is huge.

hopefuly this gris will come out formeted correct - see below the resutl for MAX and what I want.

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

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

Tue, 16 Mar 2010 20:38:08 GMT

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

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

mike_garcia — Thu, 18 Mar 2010 03:11:11 GMT

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.

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

Thu, 18 Mar 2010 05:26:36 GMT

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

Cheers,

Dave

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

mike_garcia — Thu, 18 Mar 2010 21:39:30 GMT

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.

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

Fri, 19 Mar 2010 02:08:00 GMT

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

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

Sat, 20 Mar 2010 03:58:11 GMT

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

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

Sat, 20 Mar 2010 13:41:44 GMT


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

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

Sat, 20 Mar 2010 14:02:19 GMT


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>