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.

[mike_garcia]

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.

[#3]

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

[mike_garcia]

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.

[#5]

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

[mike_garcia]

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.

[#7]

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

[#8]

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

[mike_garcia]

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.

[#10]

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