Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi There
I am trying to create a year on year comparison on my dashboard
I have two text boxes one displaying current year sales and another displaying prev year sales
Current Year" =num(sum({$< [Fiscal Year] = {$(vMAXYEAR)},[Year-Month]={"<=$(=(vMAXYM))"}>} [Invoiced Amount])/1000,'#,##0')"
Previous Year="=vPREVYEAR&' : '&num(sum({$< [Fiscal Year] = {$(vPREVYEAR)},[Year-Month]={"<=$(=(vMAXLASTYEAR))"}>} [Invoiced Amount])/1000,'#,##0')"
The problem with the previous year is that it doesnt display MTD for the current month , so for example for 2014 it is displaying the full month of Jan comparing the two list boxes this is incorrect .
I have tried "If( Day(DATE_KEY) <= Day(Today()), 1, 0) as IsInMTD" in the script and then
using the flag in the set analysis expression as per below
"=vPREVYEAR&' : '&num(sum({$< [Fiscal Year] = {$(vPREVYEAR)},[Year-Month]={"<=$(=(vMAXLASTYEAR))"},IsInMTD={1}>} [Invoiced Amount])/1000,'#,##0')"
This doesnt give me correct figures... any idea what I could be doing wrong... or perhaps a simpler solution ?
I need to use the expression in a number of places so im trying to keep the expression as simple as possible as not to use up to much resources...
Thank you .
I don't see any obvious issue in what you posted. Can you post a document that demonstrates the problem?
Add a test table box showing DATEKEY and IsInMTD to test that the flag is correct in each year.
Hi Gysbert
My problem here seems to be that "If( Day(DATE_KEY) <= Day(Today()), 1, 0) as IsInMTD only gives
the current month as a flag, I would need the previous months of the fiscal year + the amount of days in the current month
Sum({<IsInMTD={1},[Fiscal Year]={$(vPREVYEAR)}>}[Invoiced Amount])
will only give me the invoiced amount last years fiscal month equivalent to this year
so in this case this months fiscal month is 4 and the above expression only gives the invoiced amount for 2014 fiscal month 4(Which is the current month)
I need an accumulative figure fiscal month 1+2+3+4(up until todays date)
so that I can do the comparison ,current year vs Prev year
I hope my explanation is not to confusing
Thank you