Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
Trying to get my previous sales from last month subtracted from the current sales this month, so it shows the amount of sales (less or more) than the previous.
My current set analysis:
Count({<Month={">=$(=MonthStart(AddMonths(Max(Month),-1))) $(=MonthStart(AddMonths(Max(Month),0)))"}>}Sales_total)
This giving the wrong result, obviously. Not too sure how to structure my analysis. Any assistance would help.
Thanks.
How about this then....
Count({<Month={"$(='>=' & Date(MonthStart(Max(Month),-1)), 'YYYY-MM') & '<' & Date(MonthStart(Max(Month),0), 'YYYY-MM'))"}>} Sales_total)
Gives me the same result as my original analysis!
HI try MonthID instead of Month,
Count({<MonthID ={"$(=MonthStart(AddMonths(Max(MonthID),-1)))Sales_total) //This is last month
-
Count({<MonthID ={"$(=MonthStart(Max(MonthID))Sales_total) //This is current month
//Subtraction is variance result
{<[Month]={"$(=max([Month]))"}>} = Current
{<[Month]={"$(=max([Month]-1))"}>} = Previous
Does this work?
What were you getting earlier? I thought you were not able to get any output before... Can you provide more detail as to what is the issue?
Dimension: (Year)Month - Format YYYY-MM.
(Example using the filter option on dimension.)
2015/05 - Count of sales: 30,000 (where sales >0)
2016/06 - Count of sales: 29,000
Expression chucks out a number above 30,000 for the 2015/05 - 2016/06.
Count({<MONTH={"$(='>=' & Date(MonthStart(Max(MONTH),-1)), 'YYYY-MM') & '<' & Date(MonthStart(Max(MONTH),0), 'YYYY-MM'))"}>} SALES_TOTAL)
It's posible I'm doing this wrong, haha. Thanks.
How about adding this -> Sales_total={">0"}
Count({<MONTH={"$(='>=' & Date(MonthStart(Max(MONTH),-1)), 'YYYY-MM') & '<' & Date(MonthStart(Max(MONTH),0), 'YYYY-MM'))"}, Sales_total={">0"}>} SALES_TOTAL)
This is very close, however it's only calculating the count of sales for the current month and not the previous!
I think it has something to do with the month dimension?
Need to (minus) the previous month to show if it's positive or negative growth.
Can you check what do you get when you put this in a text box object?
='>=' & Date(MonthStart(Max(MONTH),-1)), 'YYYY-MM') & '<' & Date(MonthStart(Max(MONTH),0), 'YYYY-MM')
Your data looks in YYYY/MM format and in your set analysis you are using the format YYYY-MM..
Also, is your date a date field or a string field?
hth
Sasi