Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I am trying to get sales for the selected (through a filter pane) Year-Month and I want also to calculate the sales for the same month but previous year.
Here is what I have so far:
sum(
{1<Accumulator_Type={'NEW_ORDER'},Order_Channel={'DIRECT'}
,[Transaction_Date.autoCalendar.Year]={"=$(=vLY)"}
,[Transaction_Date.autoCalendar.Month]]={"=$(=vMonth)"}>}
Value-Tax)
where vLY = Year([Transaction_Date.autoCalendar.YearMonth])-1
and vMonth = =[Transaction_Date.autoCalendar.Month]
when I put these variables in text box they show the right values when I select a Year-Month
Any ideas?
Hi,
You need to use max function in order to get a static values for the year and month in the variables.
Value for previous year can be obtained by using the max function :
vLY = Year(Max([Transaction_Date.autoCalendar.YearMonth]))-1
Value for current month can be obtained, again by using the max function
vMonth = Max([Transaction_Date.autoCalendar.Month])
Expression for previous year sales is as follows :
sum({<Accumulator_Type={'NEW_ORDER'},Order_Channel={'DIRECT'}
,[Transaction_Date.autoCalendar.Year]={"=$(vLY)"}
,[Transaction_Date.autoCalendar.Month]]={"=$(vMonth)"}>}
Value-Tax)
Hope this helps.
Best Regards,
Vikraant
You are right although max is optional. What was missing was a Month() function to wrap the Transaction.autocalendar.Month
For some reason, although the value is correct in a text box, the set analysis does not accept it!!!!
Are the variables $(vMonth) and $(vLY) showing correct values in text box, even without selections?
When $(vMonth) = [Transaction_Date.autoCalendar.YearMonth] then No
When $(vMonth) = month(max([Transaction_Date.autoCalendar.YearMonth])) then yes
When $(vMonth) = max([Transaction_Date.autoCalendar.YearMonth]) then i get the value 43132