topic Re: Opening Stock in QlikView

Opening Stock

gautik92 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Opening Stock

lalitkgehlot89 — Wed, 15 Jun 2016 04:46:43 GMT

Hi Gowtham,

Please provide sample data.

Re: Opening Stock

ajsjoshua — Wed, 15 Jun 2016 04:52:42 GMT

Hi Bro,

How many dimensions you are using in pivot.

when you sort a pivot table, you have to sort it first according to the first dimension, then according to the next, etc.

Re: Opening Stock

gautik92 — Wed, 15 Jun 2016 04:54:37 GMT

Hi bro ajsjoshua

sorting is not the problem now i want to shoe opening stock value

Re: Opening Stock

gautik92 — Wed, 15 Jun 2016 04:55:40 GMT

See the attachment

Re: Opening Stock

ajsjoshua — Wed, 15 Jun 2016 04:59:03 GMT

Hi Bro,

share ur expression.

Re: Opening Stock

gautik92 — Wed, 15 Jun 2016 05:10:36 GMT

I have shared the application da check t

Re: Opening Stock

MayilVahanan — Wed, 15 Jun 2016 05:27:21 GMT

Try like this

PICK(Match(%EXPRESSIONS,'OPENINGSTOCKNETWEIGHT','OPENINGSTONEWEIGHT'),

if(GetSelectedCount(SalesFY)>0 and GetSelectedCount(Month)>0,

$(=Sum({<PURCHASERETURNDATE={"$(=Date(Min(NumDate)-1))"},Day=,Month=,SalesFY=>}StockNETWEIGHT)),

Sum({<SalesFY={$(=Max(SalesFY))},StockDateNum={"$(=Floor(MonthEnd(StockDateNum,-1)) )"},Month=>}StockNETWEIGHT)),

Implement this in every expression logic

Re: Opening Stock

gautik92 — Wed, 15 Jun 2016 05:55:56 GMT

in if first expression is working fine

second one is not working

Re: Opening Stock

gautik92 — Wed, 15 Jun 2016 06:27:11 GMT

if month & year is selected it is working fine.if i clear it is not working

Re: Opening Stock

pho3nix90 — Wed, 15 Jun 2016 08:37:56 GMT

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.

Re: Opening Stock

gautik92 — Wed, 15 Jun 2016 08:54:53 GMT

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

Re: Opening Stock

pho3nix90 — Wed, 15 Jun 2016 09:10:37 GMT

OKay,

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

Re: Opening Stock

gautik92 — Wed, 15 Jun 2016 09:11:33 GMT

opening stock

closing stock is fine

Re: Opening Stock

pho3nix90 — Wed, 15 Jun 2016 09:37:01 GMT

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.