Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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.
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.
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
Thanks!
I have to check a couple of things but maybe this could be good alternative.