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

Finding Date when reaches target in straight table

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!

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

4 Replies
pover
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

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.

swuehl
MVP
MVP

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

Not applicable
Author

Thanks!

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