Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
gautik92
Specialist III
Specialist III

Opening Stock

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

14 Replies
pho3nix90
Creator II
Creator II

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
Specialist III
Specialist III
Author

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

pho3nix90
Creator II
Creator II

OKay,

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

gautik92
Specialist III
Specialist III
Author

opening stock

closing stock is fine

pho3nix90
Creator II
Creator II

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.