Set Analysis With a Date Variable

[#1]

Hey everyone, I am having some issues with my set analysis. This is what I currently have.

Variable:

vMaxTransDate = max(TranDate)

Set Analysis:

=sum({<SalesStat = {'Open'}, TranDate = {'=$(vMaxTransDate)'}>}SalesQty)

This is not giving me the SalesQty for that max date but instead its giving me all the SalesQty till that max date. Would love some help with this. Thank you in advance.

[sunny_talwar · Accepted solution]

Try one of these:

=Sum({<SalesStat = {'Open'}, TranDate = {'$(=$(vMaxTransDate))'}>}SalesQty)

or

=Sum({<SalesStat = {'Open'}, TranDate = {'$(=vMaxTransDate)'}>}SalesQty)

[swuehl]

Using an equal sign in the field modifier starts an advanced search, which you don't want here, you want to use a constant value element list.

Take care to format your value according to your format of TranDate:

Variable:

vMaxTransDate

=Date(max(TranDate),'MM/DD/YYYY')

// Format according to your TranDate field format and use a leading equal sign in definition

Set Analysis:

=sum({<SalesStat = {'Open'}, TranDate = {'$(vMaxTransDate)'}>} SalesQty)

Dates in Set Analysis

The Magic of Variables

[#4]

=sum({<SalesStat = {'Open'}, TranDate ={'$(=$(vMaxTransDate))'}>}SalesQty)

That worked. Thank you. Can you explain why this worked?

[swuehl]

That worked. Thank you. Can you explain why this worked?

I assume you haven't defined your variable with a leading equal sign, so you need the equal sign in the set modifier to evaluate your max() function, right?

[#6]

Yeah, you are correct. Thank you.

[wiz_nerd]

Small changes that makes calculation work. 🙂