0 Replies Latest reply: Sep 22, 2009 11:10 PM by 2crabs RSS

    using IFs and Set Analysis in a calculated dimension

      All - my first post. I am trying to add a calculated dimension to a chart. My goal is for each data point to represent 12 months.

      For the field InvoiceDate, I can calculate the range I want with:

      =if(month(InvoiceDate)=12,

      makedate(year(InvoiceDate),month(InvoiceDate)-11) & ' to ' & makedate(year(InvoiceDate)+1,1),

      makedate(year(InvoiceDate)-1,month(InvoiceDate)+1) & ' to BEFORE ' & makedate(year(InvoiceDate),month(InvoiceDate)+1))

       

      This works sufficiently to 'see' the range I would get on a given date. But when I try to apply the general case using set analysis, I don't know how to properly format.

       

      Here's what I have:

       

      = if(month(InvoiceDate)=12,

      {$<InvoiceDate= {">=$(=makedate(year(InvoiceDate),month(InvoiceDate)-11))<$(=makedate(year(InvoiceDate)+1,1))"}>},

       

      {$<InvoiceDate={">=$(=makedate(year(InvoiceDate)-1,month(InvoiceDate)+1))<$(=makedate(year(InvoiceDate),month(InvoiceDate)+1)"}>}

      )

       

      I realize I'm trying to redefine Invoice Date (probably incorrect) but don't know what else to do. Thanks in advance,

       

      Crabby!