Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am trying to calculate on a chart the average sale of Product1 in the last 8 weeks starting from last week. I have been able to come up with the following expression but it seems not to work:
=Num((sum({<Sale_Week={'>$(=Week(Today())-1 ))<=$(=Week(Today())-9 ))'} >} Product1)
/ Count({<Sale_Week={'>$(=Week(Today())-1 ))<=$(=Week(Today())-9 ))'} >} Product1)),'##%')
Please kindly help me review and adjust accordingly.
Regards
try this with distinct:
=Num(
(sum({<Sale_Week={'>$(=Week(Today())-1 ))<=$(=Week(Today())-9 ))'} >} Product1)
/ Count({<Sale_Week={'>$(=Week(Today())-1 ))<=$(=Week(Today())-9 ))'} >} distinct Product1))
,'##%')
I think you are using wrong order as well, try like:
=Num(
(sum({<Sale_Week={'>$(=Week(Today())-9 ))<=$(=Week(Today())-1 ))'} >} Product1)
/ Count({<Sale_Week={'>$(=Week(Today())-9 ))<=$(=Week(Today())-1 ))'} >} distinct Product1))
,'##%')
What is the format of your field Sales_Week? Can you share some data?
You have an mismatched parentheses, but I would propose the following:
=sum({<Sale_Week={"<$(=Week(Today() - 7))>=$(=Week(Today() - 63))"}>} Product1)
/ Count({<Sale_Week={"<$(=Week(Today() - 7))>=$(=Week(Today() - 63))"}>} Product1)
Or you may need (depending on your data/requirements):
=sum({<Sale_Week={"<$(=Week(Today() - 7))>=$(=Week(Today() - 63))"}>} Product1)
/ Count({<Sale_Week={"<$(=Week(Today() - 7))>=$(=Week(Today() - 63))"}>} distinct Product1)
Or maybe just:
=avg({<Sale_Week={"<$(=Week(Today() - 7))>=$(=Week(Today() - 63))"}>} Product1)
But none of these methods will work over a year boundary. You need a year field and/or a week sequence field or year/week field to handle the year boundaries correctly. What calendar fields do you have that could be used for this purpose?
Hi all,
Currently I have the following which works; however, I will like to set a date parameter to it.
=num(sum({$<Sale_Week=>} Product1)/
count({$<Sale_Week= >} Product1), '##%')
The parameter is I want last 8 weeks starting from last week
Sales_Week is number for calendar year: 1 to 52
The actual Sales date is showing like "42087, 42088,42089" and so on.
May be try like this:
=Num((Sum({<Sale_Date={"$(='>' Num(Today()-7) & '<=' & Num(Today() - 63))"}>} Product1) / Count({<Sale_Date={"$(='>' Num(Today()-7) & '<=' & Num(Today() - 63))"}>} Product1)), '##%')
Where this -> ='>' Num(Today()-7) & '<=' & Num(Today() - 63) in a text box object should give you the range you are looking for in the same format as your Sale_Date field (in your case you mentioned that Date is in number format)
Okay,
then this should work:
=num(
sum({$<Sale_Week={'>=$(=max(WeekField)-8)<=$(=max(WeekField)'}, Sale_Week= >} Product1)/
count({$<Sale_Week={'>=$(=max(WeekField)-8)<=$(=max(WeekField)'}, Sale_Week= >} Distinct Product1)
, '##%')
Hope it will help!!