Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to compare sales of current year vs last year. for this i have used this expression
sum({$<Year={$(=only(Year))}>} LineSalesAmount)
sum({$<Year={$(=only(Year)-1)}>} LineSalesAmount)
and it works fine when i select a year field
But suppose when i dont select any year, i want the comparision between current year vs previous all years
Hi,
The Only gives you Null when you have multiple values.
You could use the Max() function instead.
Then, if you dont have any year selected the expression will use the highest year (I.e current year)...
sum({$<Year={$(=max(Year))}>} LineSalesAmount)
sum({$<Year={$(=max(Year)-1)}>} LineSalesAmount)
Hi,
The Only gives you Null when you have multiple values.
You could use the Max() function instead.
Then, if you dont have any year selected the expression will use the highest year (I.e current year)...
sum({$<Year={$(=max(Year))}>} LineSalesAmount)
sum({$<Year={$(=max(Year)-1)}>} LineSalesAmount)
Suppose i have sales data from year 2005 to 2011. then how to see the sales of 2005 to 2010 vs sales of 2011
You just alter the second expression to something like the following...
sum({$<Year={$(=max(Year))}>} LineSalesAmount)
sum({$<Year={"<=$(=max(Year)-1)"}>} LineSalesAmount)
The first expression is the same.
The second expression says that Years shall be less or equal to Previous year.
Of course the following is the same:
sum({$<Year={"<$(=max(Year))"}>} LineSalesAmount)
Year shall be less to current year.
You can also have a intervall:
sum({$<Year={"<$(=max(Year))>$(=max(Year)-5)"}>} LineSalesAmount)
Year shall be less than current year and also more than current year-5
My next query is...
suppose i select year 2011 and month sep....
now i have to see sales of 2011 for jan to aug vs 2011 for sep
You can do the same for months.
Expand your set expression to include month
sum({$<Year={$(=max(Year))},Month={"$(=max(Month))"}>} LineSalesAmount)
sum({$<Year={"$(=max(Year))"},Month={"<$(=max(Month))"}>} LineSalesAmount)
First expression current Year and current Month
Second expression:
Current Year and Months less than current Month
Month must contain the Months part of a date. I.e It must be a dual value to work correct.
If Month just only contains the text part of the name t vill do a text comparisson and thats is not what you want...
As long as Month is declared as something as Month = Month(<YourDate>); in script you propobly will be fine.
If you have the Monthsnumber in a field and want to use that insted, you could do that but you must cancel the month selection if you have some...
sum({$<Year={$(=max(Year))},Month=,MonthNumber={"$(=max(MonthNumber))"}>} LineSalesAmount)
sum({$<Year={"$(=max(Year))"},Month=,MonthNumber={"<$(=max(MonthNumber))"}>} LineSalesAmount)
Here I use MonthNumber which is associated with Month.
But I use Month= , to cancel out Months influense on the expression since you use a $ as a set identifier.
I recomend you to study the set analysis portion of the help file. It can be where useful...
it is showing the same values..on selecting may 2011
Hi,
do you have date field?
sum({<DateField={">=$(=YearStart(Max(DateField)))<=$(=MonthEnd(Max(DateField),-1))"},Year=,Month=>} LineSalesAmount) //sales of 2011 for jan to aug
sum({<DateField={">=$(=MonthStart(Max(DateField)))<=$(=MonthEnd(Max(DateField)))"},Year=,Month=>} LineSalesAmount)// 2011 for sep
Celambarasan
i have order date field.....i have orderdate in place of date field .it is working