4 Replies Latest reply: Jul 8, 2012 11:53 PM by Kiran Rokkam RSS

    count field with previous month

      Hi

       

      I 'm new to this tool and i need kind help seniors..... could u pls help me out

       

      I am using Line graph and in my expression i hav to include this logic

      I hav a date field "X" and i want to count "Y" with the condition that count should include X+ previous 6 month and my dimenion is also "X"

      i tried this logic

      count(DISTINCT(if(Date(X)<monthstart(AddMonths(X,-6)) ,Y))

       

      'm getting wrong value when i look at the source data i dont know where exactly 'm running out of logic so guide me out seniors...

       

       

      many thanks in advance

       

       

         

        • Re: count field with previous month

          Try:

           

          Count(Distinct {<X={"<=$(=monthstart(AddMonths(X,-6)))"}>} Y). If X is a number then change it to (num(monthstart...).

           

          Kiran Rokkam.

          • Re: count field with previous month

            Expression tat 'm using currently is tat

             

            count(DISTINCT(if(Date(DisputeLog.DateofLogging)<monthstart(AddMonths(DisputeLog.DateofLogging,6))

            and date(DisputeLog.DisputePeriod)<MonthStart(AddMonths(DisputeLog.DisputePeriod,6))

            and DisputeLog.DisputeType<>'Information' and

            DisputeLog.Source<>'DecMes' and

            DisputeLog.Status='Completed',DisputeLog.LogId)))

             

             

            my two date fields are DateOfLogging and DisputePeriod and few conditions.... if i select some month for eg..... Mar-2012 i want to show the count Mar-2012 + Feb-2012.....+Oct-2011  and if i select Apr-11 it should giv me the count again Apr-11 + previous 6 months data like dynamic one and 'm using same date field as my dimension i used ur expression its showing 0 value.... could u pls help me out?

              • Re: count field with previous month

                Try this:

                 

                For this to work ensure both your date fields are timestamps:

                count(DISTINCT {<DisputeLog.Status={'Completed'},DisputeLog.Source-='DecMes'},DisputeLog.DisputeType-={'Information'},DisputeLog.DateofLogging={"<=$(=monthstart(AddMonths(Date(Max(DisputeLog.DateofLogging)),-6)))"},DisputeLog.DisputePeriod={"<=$(=monthstart(AddMonths(Date(Max(DisputeLog.DisputePeriod)),-6)))"}>} DisputeLog.LogId)

                 

                 

                Regards,

                Kiran Rokkam.