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?

[sunny_talwar]

There might be more scenarios, but if your variable is not declared with an equal sign... you would need the first dollar sign expansion to evaluate your variable.... once that is done... you would need the second one to evaluate within your set analysis.

[Chanty4u]

this is only am looking for    thanks sunny

[sunny_talwar]

Hahaha okay ...

also, did you check my two attachments? I wonder if your set analysis is actually working or if you think it is working, but it really isn't working?

[Chanty4u]

hahah   yes sunny its working   Numbers are matching with data which we used .

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

this used by Kaushik -  without "="  this should work right?     as in variable value is      =max(year)   something

[sunny_talwar]

hahah   yes sunny its working   Numbers are matching with data which we used .

Not sure if it is possible for you or not, but would you be able to create a mock up please?

[sunny_talwar]

I am not sure when it works, but I know in this case it wouldn't because the variable expands to an expression which needs to be further expanded

[Chanty4u]

okay sunny   thanks

[Chanty4u]

the same variables i tried its showing null to me  

{">=$(=$(vMaxPendingDate))"}      is this fine ?

[sunny_talwar]

What is vMaxPendingDate? I mean where did this variable come from?

[Chanty4u]

variable value is

=max(PendingDateNum)