13 Replies Latest reply: Feb 20, 2013 8:22 AM by David Levine RSS

    Set Expression with Dates

      I don't think this should be too hard but having trouble getting the set expression to work:

       

      I am trying to sum on a field called demand where the demand occurs in the current month + 1

       

      For examples - I have a series of demand entries - time with different dates attached in the future.  I want to pull in the demand where the date is in March (Feb + 1). 

       

      My fields are:

      - DemandMonth - this is the month where the demand occurs

      - Demand - this is the amount of hours

       

      I have a master calendar that is linked to the date and creates the demandmonth field

        • Re: Set Expression with Dates
          Martin FAVIER

          Hi,

           

          If your field DemandMonth is numeric, I suggest you to do that :

          Data:

          LOAD ID,

               ...,

               MakeDate(Year(Today()), DemandMonth) as DemandDate,

               Demand

          FROM Data.qvd;

           

          Calendar:

          LOAD Distinct DemandDate,

               MonthName(DemandDate) as DemandYearMonth,

               Month(DemandDate) as DemandMonth

          RESIDENT Data;

          After that, you have to use something like this in design part :

          Sum({<DemandMonth={"$(=MonthName(Today(),1))"}>} Demand)

           

          Hope that helps you

           

          Martin Favier

          • Re: Set Expression with Dates
            Martin FAVIER

            Hi,

             

            If your field DemandMonth is numeric, I suggest you to do that :

            Data:

            LOAD ID,

                 ...,

                 MakeDate(Year(Today()), DemandMonth) as DemandDate,

                 Demand

            FROM Data.qvd;

             

            Calendar:

            LOAD Distinct DemandDate,

                 MonthName(DemandDate) as DemandYearMonth,

                 Month(DemandDate) as DemandMonth

            RESIDENT Data;

            After that, you have to use something like this in design part :

            Sum({<DemandMonth={"$(=MonthName(Today(),1))"}>} Demand)

             

            Hope that helps you

             

            Martin Favier

            • Re: Set Expression with Dates
              Martin FAVIER

              Hi,

               

              If your field DemandMonth is numeric, I suggest you to do that :

              Data:

              LOAD ID,

                   ...,

                   MakeDate(Year(Today()), DemandMonth) as DemandDate,

                   Demand

              FROM Data.qvd;

               

              Calendar:

              LOAD Distinct DemandDate,

                   MonthName(DemandDate) as DemandYearMonth,

                   Month(DemandDate) as DemandMonth

              RESIDENT Data;

              After that, you have to use something like this in design part :

              Sum({<DemandMonth={"$(=MonthName(Today(),1))"}>} Demand)

               

              Hope that helps you

               

              Martin Favier

              • Re: Set Expression with Dates
                jagan mohan rao appala

                Hi,

                 

                Try this expression, suppose if you select Feb-2013, then the below expression will give you the demand for Mar-2013

                 

                 

                =Sum({<DateField={'>=$(=AddMonths(MonthStart(Max(DateField)), 1)<=$(=AddMonths(MonthEnd(Max(DateField)), 1))'}>} Demand)

                 

                Hope this helps you.

                 

                Regards,

                Jagan.

                  • Re: Set Expression with Dates

                    Thanks - just to clarify - really I am looking to use the system month as the comparison tool - vs entering a date.  Also - I did link the demanddate field on the table to a master calendar to create fields for Demandweek, demandyear, demandmonth and demandday.  So really this should be a simple comparison of the current(system) month +1 month to the demandmonth field - and then a sum of the demand. 

                  • Re: Set Expression with Dates

                    I tried this but it in not returning the values for March - current month +1

                    Sum({<[Demand Month] = {$(AddMonths(Month,1))