Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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,
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
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)
Thanks for your reply. I will try and let you know if I get the desired result.
Regards
Chintan