topic Finding Date when reaches target in straight table in QlikView

Finding Date when reaches target in straight table

Tue, 17 Jan 2012 18:46:10 GMT

Hello!

I have 2 tables.

Lets say table1 is:

TargetUnits

TargetDate

Product

Table2 is:

Date

Units

Product

And I need to show

Product

TargetDate

TargetUnits

Date reaching target

I cant find the way to acumulate sum until reaching the target by product and obtain the corresponding date.

Thanks in advance!

Re: Finding Date when reaches target in straight table

pover — Tue, 17 Jan 2012 19:03:28 GMT

You should able to do something like the following formula to calculate the average number of units sold per day and divide that by the target units to get the number of days needed to reach the target units. You would then add that number of days to starting date.

min(Date) + sum(TargetUnits) / (sum(Units)/(max(Date)-min(Date)))

Karl

Edit: Oops, I thought the column was the date when the target is to be reached and not the date the target was reached.

Finding Date when reaches target in straight table

Tue, 17 Jan 2012 19:09:12 GMT

I think the problem there is that the date may not be accurate because the transactions aren't linear.

Day 1 can move 30 units and day 2 500 for instance.

Re: Finding Date when reaches target in straight table

swuehl — Tue, 17 Jan 2012 19:26:35 GMT

Maybe like attached?

This uses an accumulation in the script and a flag to indicate when the threshold is reached.

I think it could get quite hard to do this in the front end only. It looks like a enforced version of a pareto chart (how many days needed to get a threshold (per product) vs. how may customers make up some share of revenue). So I was hoping to get some support by the new dimension limits in QV11, but have not succeeded yet.

Regards,

Stefan

P.S. If you can't open my attachment:

Script to create sample data:

Target:

LOAD * INLINE [

TargetUnits, TargetDate, Product

100, 17.01.2012, P1

120, 18.01.2012, P2

];

Temp_Products:

LOAD Date(MakeDate(2012)+recno()-1) as Date,

'P'&ceil(RAND()*2) as Product,

ceil(RAND()*30) as Units

AutoGenerate 30;

Products:

LOAD *,

if(AccUnits >= Lookup('TargetUnits','Product',Product,'Target'), 1,0) as Flag;

LOAD *

, if(Peek(Product)=Product,rangesum(Peek(AccUnits),Units), Units) as AccUnits

resident Temp_Products order by Product, Date;

drop table Temp_Products;

Then Expression in chart with dimensions Product:

=FirstSortedValue({<Flag={1}>} AccUnits, Date)

and

=min({<Flag = {1}>} Date

Finding Date when reaches target in straight table

Tue, 17 Jan 2012 19:39:10 GMT

Thanks!

I have to check a couple of things but maybe this could be good alternative.