Average product sale in the last 8 weeks

[#1]

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

[Anonymous]

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))

,'##%')

[Anonymous]

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))

,'##%')

[sunny_talwar]

What is the format of your field Sales_Week? Can you share some data?

[jonathandienst]

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)

[jonathandienst]

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?

[#7]

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

[#8]

Sales_Week is number for calendar year: 1 to 52

The actual Sales date is showing like "42087, 42088,42089" and so on.

[sunny_talwar]

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)

[Anonymous]

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!!