Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi experts,
I have the following tables :
What I would like to implement is a front end measure that displays the date of when target sales quantity (cumulatively speaking) was achieved.
With product_id 825 in my example, i want my measure to display "06-03-23", since it is on this date that the all time target sale quantity of 10 products was achieved (by reaching 11 in facts).
I have tried with FirstSortedValue() and RangeSum() but can't really find a way to make it work.
Due to very eratic sales pattern, linear interpolation between first sale date and last sale date is not a viable option.
Has anyone any idea on how to achieve this ?
Best Regards,
Tech.
Hi, try this code:
Script:
LOAD
sale_id,
sale_product_id,
Date#(sale_date, 'DD-MM-YYYY') as sale_date,
sale_qty
INLINE [
sale_id, sale_product_id, sale_date, sale_qty
234, 825, 01-01-2023, 5
980, 825, 06-03-2023, 6
456, 678, 12-03-2023, 7
];
Set analisys:
Date(min(aggr(
if(
RangeSum(
Above(
Sum(sale_qty),
0,
RowNo()
)
) >= 10,
sale_date
),
sale_product_id,
sale_date
)),'DD-MM-YYYY')
Regarts.
Hi,
My expression :
Date(Min(
Aggr(
if(rangesum( above( sum(sale_qty),0,rowno()))>target, sale_date),
sale_product_id,
sale_date
)
))
With this data :
data:
Load
sale_id,
sale_product_id,
Date#(sale_date, 'MM-DD-YY') as sale_date,
sale_qty
Inline [
sale_id, sale_product_id, sale_date, sale_qty
234, 825, 01-01-23, 5
235, 825, 01-01-23, 2
980, 825, 06-03-23, 6
980, 825, 08-03-23, 6
456, 678, 12-03-23, 1
];
target:
Load
*
Inline [
sale_product_id, target
825, 10
678, 3
]
Hi, try this code:
Script:
LOAD
sale_id,
sale_product_id,
Date#(sale_date, 'DD-MM-YYYY') as sale_date,
sale_qty
INLINE [
sale_id, sale_product_id, sale_date, sale_qty
234, 825, 01-01-2023, 5
980, 825, 06-03-2023, 6
456, 678, 12-03-2023, 7
];
Set analisys:
Date(min(aggr(
if(
RangeSum(
Above(
Sum(sale_qty),
0,
RowNo()
)
) >= 10,
sale_date
),
sale_product_id,
sale_date
)),'DD-MM-YYYY')
Regarts.
Hi,
My expression :
Date(Min(
Aggr(
if(rangesum( above( sum(sale_qty),0,rowno()))>target, sale_date),
sale_product_id,
sale_date
)
))
With this data :
data:
Load
sale_id,
sale_product_id,
Date#(sale_date, 'MM-DD-YY') as sale_date,
sale_qty
Inline [
sale_id, sale_product_id, sale_date, sale_qty
234, 825, 01-01-23, 5
235, 825, 01-01-23, 2
980, 825, 06-03-23, 6
980, 825, 08-03-23, 6
456, 678, 12-03-23, 1
];
target:
Load
*
Inline [
sale_product_id, target
825, 10
678, 3
]
Thanks @cristianj23a , it worked like a charm !
I just changed the target to be dynamic (different for each product) and it worked.
Thanks again, amazing to see how fast the community is !
Thanks also to @Aurelien_Martinez, both solutions are working.
Tech