Compartively analysis

[#1]

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

[#2 · Accepted solution]

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)

[#3]

Suppose i have sales data from year 2005 to 2011. then how to see the sales of 2005 to 2010 vs sales of 2011

[#4]

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

[#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

[#6]

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...

[#7]

it is showing the same values..on selecting may 2011

[CELAMBARASAN]

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

[#9]

i have order date field.....i have orderdate in place of date field .it is  working