1 Reply Latest reply: Oct 30, 2012 3:07 PM by Gysbert Wassenaar RSS

    ¿value problems of past dates, inventories?

      Hi everyone, I have the following ratio of sales and inventories by date:

       

      Sin título.png

       

      I need to get two data, if I select the date 03/10/2012 (inside a sheet), obtain the inventory of the selected week (03/10/2012) and the week previous from the one selected (26/10/2012), (final inventory and initial inventory respectively), the first thing I did was to define a pair of variables, for initial and final dates, through a calendar object, inside the script:

       

      CalendarTemp:

      LOAD

      Max(FECHA) AS fechaMax,

      Min(FECHA)-7 AS fechaMin

      RESIDENT BASE;

       

       

      LET vMaxFECHA = FieldValue('fechaMax', 1);

      LET vMinFECHA = FieldValue('fechaMin', 1);

       

       

      DROP TABLE CalendarTemp;

       

       

      MasterCalendar:

      LOAD

      Date(IterNo() + Date($(vMinFECHA))) AS CalendarDate,

      Week(Date(IterNo() + Date($(vMinFECHA)))) AS CalendarWeek,

      Month(Date(IterNo() + Date($(vMinFECHA)))) AS CalendarMonth,

      Year(Date(IterNo() + Date($(vMinFECHA)))) AS CalendarYear,

      InYearToDate(Date(IterNo() + Date($(vMinFECHA))), Date($(vMaxFECHA)), 0) AS YTDFlag,

      InYearToDate(Date(IterNo() + Date($(vMinFECHA))), Date($(vMaxFECHA)), -1) AS LYFlag

      AUTOGENERATE 1 WHILE Date(IterNo() + Date($(vMinFECHA))) <= Date($(vMaxFECHA));

       

       

      LET vSemanaMax = Week($(vMaxFECHA));

      LET vSemanaMin = Week($(vMinFECHA));

       

       

      And inside a sheet, in a simple table, I defined the next expression:

       

      inventario inicial: =sum({<FECHA={$(vSemanaMin}>}[INVENTARIO])

      inventario final: = sum({<FECHA={$(vSemanaMax}>}[INVENTARIO])

       

      The problem is that when I choose different options in the sheet, the variables always show the same initial and final weeks independently of the selections I make, I need these variables to move according to the selections I make in the sheet.

       

      And the other thing is that the expression of initial and final inventory give me no results, CAN SOMEONE HELP ME WITH THIS????