2 Replies Latest reply: Apr 16, 2015 4:46 PM by Ziad Mohammad RSS

    Nested if Statement to create Sub-Fields from my Date Field

      Hi There,

      I'm a bit stuck on a small problem I have and any suggestions would be much appreciated......

       

      I have a Date field  'Invoice_Date' being used as a dimension in a bar chart.

      I would like to group these invoices into 6 catagories Based on their due date, ie. whether the invoice is due at some point in the future or at some point in the past. These catagories would be relative to today 'today()'

       

      The categories would be as follows:         

      Invoice due in 15 days +

      Invoice due in 8-14 days

      Invoice due in 0-7 days

      Invoice overdue 1-7 days

      Invoice overdue 8-14 days

      Invoice overdue 15 days +

       

      I gather I need a calculated dimension made of a chain of 'if' statements but cant quite get it to work.

      Any suggestions much appreciated   : )

        • Re: Nested if Statement to create Sub-Fields from my Date Field
          Massimo Grossi

          if d is the date field add a calculated dimension

           

          =

          if(d<(today()-15), dual('<15',-15),

          if(d<(today()-8), dual('<8',-8),

          if(d<today(), dual('<0',0),

          if(d<(today()+8), dual('<-8',8),

          if(d<(today()+15), dual('<-15',15),

          dual('other',100)

          )))))

          • Re: Nested if Statement to create Sub-Fields from my Date Field
            Ziad Mohammad


            Use Class function with a width of 10 days is this is ok

             

            make this as calculated dimension

             

             

            class( Today(0) - invoiceDate ,7 )

             

            and your expression

            should be the count or sum of invoices

             

            Count(Invoices)   or sum(invoices)

             

            The Class Function will return a data like this

              

            =Class(Today(0)-InvoiceDate,10)count(Value)
            93
            160 <= x < 1703
            170 <= x < 18010
            180 <= x < 19010
            190 <= x < 2008
            530 <= x < 5408
            540 <= x < 55010
            550 <= x < 56010
            560 <= x < 5703
            890 <= x < 9003
            900 <= x < 91010
            910 <= x < 92010
            920 <= x < 9308

             

            in order to make it look better use the replace function to eleiminate <= and < to -

             

            Use replace() function for replacing '<= x <' to -


            Replace(Class(Today(0)-Date,10),'<= x <','-')