8 Replies Latest reply: Nov 4, 2014 9:27 AM by Manish Kachhia RSS

    set analysis

    Anil patchava

      Hi,

      Table:

      name     sales     data

      1          200          12-01-2001

      2          300          21-01-20001

      from till date.

       

      I want ytd expression. but in this expression i dont want some dates  like jan26, aug15, and some other dates.

      ---

      Regards

      K

        • Re: set analysis
          Alessandro Saccone

          Try something like this:

           

          Sum({$ <data={'*'}-{'26/08/2014'}>} Values)

           

          let me know

          • Re: set analysis
            Manish Kachhia

            Can you provide some more data please? Looks like you want to ignore the dates having public holidays...

              • Re: Re: set analysis
                Anil patchava

                Hi,

                kindly check attached file

                 

                Regards,

                k

                  • Re: set analysis
                    Manish Kachhia

                    For 1st Bar Chart

                    Create a Bar Chart without any Dimension

                     

                    Expression1

                    Label

                    ='YTD(' &YearStart(Max({<Year = {'$(=Max(Year))'}>}[Date ID]))& '-' & Max({<Year = {'$(=Max(Year))'}>}[Date ID]) &')'

                     

                    Definition

                    =SUM({<Year = {'$(=Max(Year))'}, [Date ID] = {"<=$(=Max({<Year = {'$(=Max(Year))'}>}[Date ID]))"}>}Value)

                     

                    Expression2

                    Label

                    ='YTD(' &YearStart(Max({<Year = {'$(=Max(Year))'}>}[Date ID]),-1)& '-' & AddYears(Max({<Year = {'$(=Max(Year))'}>}[Date ID]),-1) &')'


                    Definition

                    =SUM({<Year = {'$(=Max(Year)-1)'}, [Date ID] = {"<=$(=AddYears(Max({<Year = {'$(=Max(Year))'}>}[Date ID]),-1))"}>}Value)

                     

                    =================================================================

                    For 2nd Bar Chart

                    Create a Bar Chart without any Dimension

                     

                    Expression1

                    Label

                    ='YTD(' &Date(AddYears(Max({<Year = {'$(=Max(Year))'}>}[Date ID]),-1))& '-' & Max({<Year = {'$(=Max(Year))'}>}[Date ID]) &')'


                    Definition

                    =SUM({<[Date ID] = {">=$(=Date(AddYears(Max({<Year = {'$(=Max(Year))'}>}[Date ID]),-1)))<=$(=Max({<Year = {'$(=Max(Year))'}>}[Date ID]))"}>}Value)

                     

                    Expression2

                    Label

                    ='YTD(' &Date(AddYears(Max({<Year = {'$(=Max(Year))'}>}[Date ID]),-2))& '-' & Date(AddYears(Max({<Year = {'$(=Max(Year))'}>}[Date ID]),-1)) &')'

                     

                    Definition

                    =SUM({<[Date ID] = {">=$(=Date(AddYears(Max({<Year = {'$(=Max(Year))'}>}[Date ID]),-2)))<=$(=Date(AddYears(Max({<Year = {'$(=Max(Year))'}>}[Date ID]),-1)))"}>}Value)