Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone, I have the following ratio of sales and inventories by date:
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????
You set the variables to fixed values in the load script. You need to assign new values to them if you want them to have other values. You can use triggers to set them to values you select in a listbox. Add Select in Field actions to the OnSelect triggers of the relevant fields.