Opening and closing stock for Every month - Qlik Community

vikasmahajan · ‎2013-10-01

Dear All

I want to generate following type of chart and I am attaching the excel sheet opnclosing.xls which is having

all the data opening stock data + receipts data - issue data = closing for that month for only one product

EXPECTED OUTPUT
AS PER SELECTION OF FINANCIAL YEAR
CYCLOPAM	OPN STK	TOT REC	TOT ISSUE	CLOSING
APR	2540	500	200	2840
MAY	2840	300	50	3090
JUN	3090	12000	15000	90

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.

kaushiknsolanki · ‎2013-10-01

Hi,

In your excel sheet the Total issue and Total Receipt have same data.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

vikasmahajan · ‎2013-10-01

Dear Sir

I have modified excel sheet please reply for this post ?

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.

abeyphilip · ‎2013-10-01

Hi Vikas,

I am not sure I understood your issue correctly.

Made couple of changes in your QVW (in load and the front end expressions) based on my understanding.

Please see if this is what you are looking for. If not, please explain more on the expected output.

Regards,

Abey

vikasmahajan · ‎2013-10-01

Thanks for Reply I want This is for one month this way I want to summary April to March along with closing balances.

Month	OPENING STOCK	TOTAL REC	TOTAL ISSUE	COLUMN(1)+COLUMN(2)-COLUMN(3)
Aug	1880378	4921045	4735035	2066388

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.

vikasmahajan · ‎2013-10-01

Dear Sir ,

Following are my expression when I put in opening bal. column values are appering other than aug month also I want single line Please refer attached jpg.

SUM({$<FinancialYear=,Quarter=,MonthName=,[Posting Date] ={"<$(=Monthstart(min([Posting Date])))>=01/04/2012"},Inv_Doc_Type_FLAG={'OPSTK'}>}INVQTY) /* Closing Qty of prev month*/

Awaiting for your reply.

Vikas

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.

vikasmahajan · ‎2013-10-02

Dear Sir

Any luck on this issue ?

Vikas

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.

kaushiknsolanki · ‎2013-10-03

Hi,

Here is the solution.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

vikasmahajan · ‎2013-10-03

Thanks Sir,

I will get back you after checking .

Vikas

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.

vikasmahajan · ‎2013-10-04

Dear Sir,

I have take Variable vinvOpn using following formula

=SUM({$<FinancialYear=,Quarter=,MonthName=,[Posting Date] ={"<$(=Monthstart(min([Posting Date])))"}, Inv_Doc_Type_FLAG={'OPSTK'}>}InvOpening)

IF you observe attached sheet I am getting 1055611 opening balance repetitive for every month where as I use formula

=iF(Rowno()=1,vInvOpn,Above(Column(4))) to show opening balance and column(4) is closing , Now my problem is opening is not changing as per closing balance , Please give your suggestions.

INV_MONTH	Product Code	=iF(Rowno()=1,vInvOpn,Above(Column(4)))	SUM({$<FiscalYear={2014},[Inv_Doc_Type_FLAG]={TOTREC} >} [Receipts] ) //sum(Receipts)	SUM({$<FiscalYear={2014},[Inv_Doc_Type_FLAG]={TOTISSU} >} [Issue] ) //sum(Issue)	=COLUMN(1)+COLUMN((2)-COLUMN(3)
4	1CY4	1055611	2892089	3006480	941220
5	1CY4	1055611	5466489	4967393	1554707
6	1CY4	1055611	5714528	5062881	1707258
7	1CY4	1055611	4486770	4698355	844026
8	1CY4	1055611	4921045	4735035	1241621

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.