Set Analysis Date Range

[cranium144]

I am attempting to compare a customer's quantity sold by part for the previous 6 months versus the company.  I have the date range code no problem.  But I'm having trouble with the set analysis to force the 6 months.  I am attempting to use a variable named v6MonthStart (inappropriately named) in the set analysis. 

The variable is defined as this:

'>=' & date(monthStart(addmonths(Today(),-6))) & ' <=' & date(MonthEnd(addmonths(Today(),-1)))

which produces this as desired.  >=9/1/2017 <=2/28/2018 (verified via text box).

My expression labeled Company 6 Months Average is:

=Sum({$<Date={$(v6MonthStart)}>} [Invoice Qty]) / 6

I'm getting no data.

Suggestions please?

[kaushiknsolanki]

Hi,

Try this.

=Sum({$<Date={"$(v6MonthStart)"}>} [Invoice Qty]) / 6

Regards,

Kaushik Solanki

[Chanty4u]

may be

=Sum({$<Date={"=$(=$(v6MonthStart))"}>} [Invoice Qty]) / 6

[sunny_talwar · Accepted solution]

Or one of these

=Sum({$<Date={"$(=v6MonthStart)"}>} [Invoice Qty]) / 6

=Sum({$<Date={"$(=$(v6MonthStart))"}>} [Invoice Qty]) / 6

[sunny_talwar]

Suresh -

I don't think the syntax is right with an equal sign before the dollar sign expansion

=Sum({$<Date={"=$(=$(v6MonthStart))"}>} [Invoice Qty]) / 6

[Chanty4u]

it working in my current app sunny

[sunny_talwar]

Can you share an example file where this is working?

[Chanty4u]

this one am using without any issues

count({<Flag={2},DateNum={"=$(=$(vMaxdate))"},ID={'NA'}>}Op_ID)

[cranium144]

This one produced a number. 

=Sum({$<Date={"$(=$(v6MonthStart))"}>} [Invoice Qty]) / 6

I ended up going with this to force all customers.

=Sum({$<Date={"$(=$(v6MonthStart))"}, [Bill To Company]=>} [Invoice Qty]) / 6

[sunny_talwar]

I will test out and see what I find. Thanks buddy