8 Replies Latest reply: Apr 12, 2012 6:30 AM by Sumit Thakur RSS

    Compartively analysis

      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

        • Compartively analysis


          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)

            • Compartively analysis

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

                • Compartively analysis

                  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

                    • Compartively analysis

                      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

                        • Re: Compartively analysis

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