issue in expression

[Anonymous]

Dear Team

I have using below expression to get last 6 months sales. It is not calculating for the last 6 months but only returns sales for the month selected. Kindly help if there is something to be modified.

=SUM ( {< Monyr = {">=$(vSTART)<$(vEND)"} >} Sales)

where variables are defined as

vSTART=  date(MonthStart(Date(max(MonthYear),'M YYYY'),-6),'M YYYY')

vEND= =  num(MONTH(MonthYear),'0') &' '  & YEAR(MonthYear)

The field Monyr is available as  1 2016, 2 2016, 3 2016,......... 10 2016  ..

Thanks & Regards

Chintan

[jonathandienst]

Is MonthYear a field or a variable?

Is it text, or is it a numeric date field?

If text, how is it formatted?

Monyr is presumably a text field. This set expression will require a numeric date value or a different format if the field is text, or the comparisons will not work,

[Anonymous]

Dear Jonathan

Thanks for your reply. Monyr is a field and made in script as below:

num(month(Date))& ' '&Year(Date) as Monyr,

data is stored as 1 2016, 2 2016, 3 2016.............10 2016 etc.

Is it that I need to use num even in vSTART variable?

Thanks & regards

Chintan

[jonathandienst]

You will need to convert Monyr to a numeric date field if you want to do this sort analysis - text compares with that format will not work. In the load, use this to define Monyr:

Date(Floor(Date), 'M yyyy') as Monyr,

Then the variables (also in the load):

Set vSTART= =Date(MonthStart(Max(MonthYear)),-6),'M YYYY');

Set vEND= = Date(Floor(MonthYear), 'M yyyy');

// Note the double = signs

Finally, your set expression

=SUM ({<Monyr = {">=$(vSTART) <$(vEND)"}>} Sales)

[Anonymous]

Thanks for your reply. I will try and let you know if I get the desired result.

Regards

Chintan