Please Help!

[gaurav2017]

Nobody is there who can solve my problem, or its impossible to do it.

I am restating the problem again:

I have tables:

FiscalCalendar:

Month	Quarter
Jan	Q4
Feb	Q4
Mar	Q4
Apr	Q1
May	Q1
Jun	Q1
Jul	Q2
Aug	Q2
Sep	Q2
Oct	Q3
Nov	Q3
Dec	Q3

StockSummary:

ProductCode	ProductName	Sizes	Month	Year	PrimarySales	SecondarySales
X050	ProductA	S	Jul	2012	143	56
X050	ProductA	S	Aug	2012	128	67
X050	ProductA	S	Sep	2012	155	44
Y100	ProductB	M	Jul	2012	232	55
Y100	ProductB	M	Aug	2012	545	45
Y100	ProductB	M	Sep	2012	897	42
Z150	ProductC	L	Jul	2012	334	57
Z150	ProductC	L	Aug	2012	122	87
Z150	ProductC	L	Sep	2012	86	298
Z150	ProductC	L	Oct	2012	97	15

OpeningSales:

ProductCode	Month	Year	OpningSales
X050	Apr	2012	1000
Y100	Apr	2012	1100
Z150	Apr	2012	1200

Problem:

1. ClosingSales = (OpeningSales+PrimarySales) - SecondarySales

2. ClosingSales (March) = OpeningSales (April)

ClosingSales(April, 2012) = OpeningSales(May, 2012)

& so on.

...........................................................................

...........................................................................

& also,

ClosingSales (Mar, 2013) = OpeningSales (April, 2013)

& so on.

................................................................................

.................................................................................

I mean it should behave like this.

Regards,

Gaurav Malhotra

[Oleg_Troyansky]

I'm not totally sure that I understand the problem...

You use the terminology that belongs to inventory management, such as:

April's closing balance = May's opening balance

May's Closing Balance = Opening Balance + Receiving transactions - Shipping transactions

June Opening Balance = May Closing Balance

For inventory balance calculations, I typically recommend pre-calculating the monthly balances in the load script, using functions previous() and peek(). It's possible to calculate the balances on the fly in chart expressions, but it's more complex and heavier on performance.

The same logic isn't quite applicable to Sales. Sales transactions are simply recorded in the month that they happened. April Sales = sum of Sales that were recorded in April, etc...

If this is a GL calculation of GL balances for the account Sales, then you can use the same logic as for the inventory, only you need to define both the Balances and the Transactions at the same level of detail - either including Size or not including Size. Assuming that you have this information by size, the fragment of the load script to calculate monthly balances could look like the following (assuming that you joined the two tables together):

load

   ProductCode,

   Size,

   Year,

   Month,

   OpeningBalance,

   PrimarySales,

   SecondarySales

   if( previous(ProductCode) <> ProductCode or Previous(Size) <> Size , Opening Balance,

      peek(ClosingBalance) + PrimarySales - SecondarySales) as ClosingBalance,

...

This is just an example of how to calculate running balances, you need to do all the prep work of getting the data into the same table...

This sort of a problem belongs to the Data Modeling training class, that I wll surely develop next after the Set Analysis class (back to our LinkedIn discussion)

cheers,

Oleg

cheers

[gaurav2017]

It is basically Stock Analysis.

My boss told me that we have Opening Stock from a Table. This Opening Stock is only maintained by our company once i.e. on the 1st month of the fiscal year. & primary Sales & secondary sales are maintained in other table.

Now we can calculate Remaining Stock by Remaining Stock = [Opening Stock + Primary Sales] - Secondary Sales

Now we are having Remaining Stock for 1st Month

Now This Remaining Stock of 1st month should be equal to the Opening Stock of 2nd month.

How I can do it in QlikView.

[Oleg_Troyansky]

Gaurav,

yes, so your problem fits the Inventory Balance calculation that I suggested in my earlier message. You need to bring the Opening Balance into the transaction table (using either Join or Mapping), and then reload the table in memory using the RESIDENT load and applying the logic described above. The only thing I forgot to mention is  that the load needs to be sorted by ProductCode, Size, Year, Month:

...

ORDER BY

   ProductCode,

   Size,

   Year,

   Month

;

[#5]

Hi Gaurav,

please find the Attached ..

Hope it helps you..

Thanks,

Bhaskar

[gaurav2017]

I can't open this qvw as i am using QV Personal Edition. Can you please paste whole thing in text format

[#7]

//Here is the script :

M:

Mapping

LOAD * INLINE [

    Month, MonthNum

    Jan, 1

    Feb, 2

    Mar, 3

    Apr, 4

    May, 5

    Jun, 6

    Jul, 7

    Aug, 8

    Sep, 9

    Oct, 10

    Nov, 11

    Dec, 12

];

A:

LOAD ProductCode,

     ProductName,

     Sizes,

     Month,

     Year,

     PrimarySales,

     SecondarySales

FROM

[Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

B:

Load *,

Date(MakeDate(Year,ApplyMap('M',Month)),'MMM-YYYY') as  YearMonth

Resident A;

Drop Table A;

C:

Load

*,

If(Peek(ProductCode)<>ProductCode ,0,Peek(ClosingBalance)) as OpeningBalance,

If(Peek(ProductCode)=ProductCode,Peek(ClosingBalance)+(PrimarySales - SecondarySales),(PrimarySales - SecondarySales)) as ClosingBalance

Resident B Order by ProductCode,YearMonth;

Drop Table B;

[gaurav2017]

SET NullDisplay="";

NULLASVALUE *;

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Transactions:

LOAD [Material Code],

     Material,

     [Depo Location],

     Year,

     Month,

     [Opening Stock]

FROM

(qvd);

Join LOAD [Material Code],

     Material,

     [Depo Location],

     Year,

     Month,

     [Primary Sales]

FROM

(qvd);

Join LOAD [Material Code],

     Material,

     [Depo Location],

     Year,

     Month,

     [Secondary Sales]

FROM

(qvd);

MasterData:

LOAD [Plant Code],

     [Sales Organisation],

     [Depo Location],

     Division

FROM

(qvd);

Mapping LOAD [Material Code],

     Category

FROM

(ooxml, embedded labels, table is Mapping);

Load [Material Code],

          Month & Year as MonthYear,

          If(Peek([Material Code])=[Material Code],Peek([Remaining Stock])) as [Opening Stock],

          If(Peek([Material Code])=[Material Code],Peek([Remaining Stock])+([Primary Sales] - [Secondary Sales]),([Primary Sales] - [Secondary Sales])) as [Remaining Stock]

          Resident Transactions

          Order by Month, Year, [Material Code];

What is wrong in it ???

[pablolabbe]

Gaurav,

  If you are a beginner and are trying to do advanced and urgent stuff, the community in this forum can´t help you in required time. All of us are here to share and collaborate not for free consulting.

   Have you get a Qlikiview Training first ?  There is somebody in your company that have mores Qlikview Skills that could help you ?  Your company can afford for consulting services for a few days and help you ?

  Community is open to help, we are here to teach you to fish rather than give you the fish. Maybe someone can give you the fish, but could take a while.

Take Care,

Pablo Labbe

[gaurav2017 · Accepted solution]

Hi there,

Thanks a lot for all of you. I finished the Inventory/Stock Analysis Application few days back with all your help. Credit goes to all of you. Most importantly, Mr. Bhaskar Reddy & Mr. Oleg Tyransky. Sorry fo being rude as i was doing whole thing in a kind of urgency & needed help.

The correct logic was something like:

If(Peek(Key)<>Key ,test23,Peek([Remaining Stock])) as [Opening Stock],

If(Peek(Key)<>Key,(test23+([Primary Sales] - [Secondary Sales])),Peek([Remaining Stock])+([Primary Sales]-[Secondary Sales])

Resident TransactionsTemp ORDER BY Key, Year, MonthNum;

Regards Gaurav Malhotra