Opening Stock

[gautik92]

Hi friends,

I want to show opening stock value

for example if i click on month Jun the opening stock value should be the value of may 31.

In text box am able to get the crct value but if i take that in pivot table with month dimension it is not showing the correct value

[pho3nix90]

The problem you are facing is with the date dimension. You cannot leave a month whiting an expression, without ignoring the actual dimension which will cause headaches and is bad practice to write unnecessary long expressions. The best solution will be in the backend/script side.

You should do a resident load from your closingstock, and do the calculation in the back-end and link it to the next month.

I would have done it for you, but the application ODBC connection so cannot do reloads.

[gautik92]

can you please modify the script and send t to me i will do reload and check

[pho3nix90]

OKay,

Do you want to show the previous day "may 31" in your explenation from the openingfact, or the closingfact?

[gautik92]

opening stock

closing stock is fine

[pho3nix90]

I would attempt something like this.

OPENINGTESTBITSTOCKMASTER:
LOAD 
  STOCKDATE+1        as      PURCHASERETURNDATE,
   // SLNO,
   STOCKMODELID,
// ApplyMap('MODELMAPPING',STOCKMODELID,Null())   as   STOCKMODEL,
    STOCKGROSSWEIGHT,
    STOCKGROSSWEIGHT*STOCKBITPURITY    as   PUREWEIGHT,
    STOCKREFERENCE,
    STOCKBRANCHCODE,
    STOCKBITPURITY,
    STOCKBITGOLDRATE,
    STOCKBITTYPE,
 //ApplyMap('MODELMAPPING',STOCKMODELID,Null())    as    STOCKMODEL,
    STOCKTYPE    as  OPSSTOCKTYPE
    WHERE STOCKDATE = MonthEnd(STOCKDATE);
SQL SELECT *
FROM OPENINGTESTBITSTOCKMASTER;

That should limit the data on the last day of the month and link it to the start of the next month.

However, these kinds of modifications are difficult to make when you cannot reload, due to the testing involved. But the example above should give you an idea of what needs to happen.