4 Replies Latest reply: Jan 10, 2013 11:39 AM by Ralph Graham RSS

    Previous 6 Weeks

      Hello,

       

      I have encountered an issue at year end in relation to a variable and set analysis that I currently have. I have a set analysis that uses a variable which looks at data for the current week as well as the last 6 weeks worth of data, the original requirement was to look at a year at a time so the below expression worked fine as it simply looked at e.g. week 46, week 46 -1, week 46 -2, etc. However a new requirement is to look at data across multiple years which proves problematic at the start of a new year as the current variable will not take into account the previous years last few weeks. The only date information I have is week and year (not a specific date) so I was thinking that a numeric value should be attached to the week-year so that a week-year, week-year -1, week-year -2 would work. Any ideas?

       

      Thanks,

       

      Ralph

       

      avg

      ({<KPI={"Total stock"},Week={"YTD",$(=vCurrentWeek),$(=(vCurrentWeek-2)),$(=(vCurrentWeek-3)),$(=(vCurrentWeek-4)),$(=(vCurrentWeek-5)),$(=(vCurrentWeek-1))}>} Data

        • Re: Previous 6 Weeks
          Gysbert Wassenaar

          Create a serial weeknumber in the script using the rowno() function. You can use the serial weeknumber instead of the original weeknumber. I don't know your data model, so I can't tell if you can use the rowno() directly on your source table or if you need to create a separate week table first with something like

           

          Weeks:

          load distinct

          rowno() as serialweek,

          week & '-' & year as week-year,

          from ...sourcetable... ;

           

          Create the week-year field in your source table too to associate the two tables

          Your set analysis expression would become something like this I think:

           

          avg

          ({<KPI={"Total stock"},Week={"YTD"}> +

            <KPI={"Total stock"},Year=,Week=,serialweek={">=$(=max(serialweek)-5)<=$(=max(serialweek))"}>}

          Data)