1 Reply Latest reply: Sep 26, 2013 12:17 PM by Jelco Schoolmeester RSS

    Pre Calculated Like For Like boolean

      Hi Guys,

       

      I'm hoping someone could help me. I work for a retail organisation with allot of stores spread globaly.

      When i would like to build a high level (Global or country for example) dashboard that shows the store performance disregarding new openings or closings of stores i would need a like for like calculation.

       

      Like for Like defenition: show only data(turnover for example) of stores that where open lastyear same period and are currently still open.

       

      I would like to build a boolean for this so users can select if they want all the figures to be like for like or not.

       

      Every Store has an openyearweekcode and a closedyearweek code. When a store is open, the closedyearweekcode is 2099W52.

      If its closed, the closedyearweekcode containts the year/week when the store is closed.

       

      I would like to build in script a following if statement:

       

      If (openyearweekcode <= currentyearweek – 52 

           and

          closedyearweekcode >=currentyearweek

      ,

      'Like For Like'

      ,

      'All Data')

       

      I've converted openyearweekcode,closedyearweekcode and yearweekcode to the same date format.

      I've also created a autonumber field on yearweekcode to cound back 52 weeks.

       

      Hopefully you could help me with the last bit!

       

      Thanks allot!