topic Re: Help for Task in QlikView

Help for Task

gaurav2017 — Fri, 20 Nov 2020 18:02:30 GMT

Hi there,

Firstly download & open the file i attached.

then i want to know if its possible to find "Stock" & if yes then how ???

The scenario:

I am having fields:

A,B,C,D,E,F,PrimarySales, SecondarySales

Given:

I have OpeningStock for 1st month of fiscal year i.e. July

I have PimarySales & SecondarySales of every month in a year.

Required:

For July,

The RemainingStock should be calculated by:

RemainingStock = (OpeningSales+PrimarySales) - SecondarySales

For August,

RemainingStock of July = OpeningStock of Aug

Regards,

Gaurav Malhotra

Re: Help for Task

gaurav2017 — Mon, 25 Jun 2012 09:57:31 GMT

Does Nobody knows what am i talking

Re: Help for Task

gaurav2017 — Tue, 26 Jun 2012 09:48:54 GMT

Please Help..........I am in great problem.

Re: Help for Task

jonathandienst — Tue, 26 Jun 2012 10:29:13 GMT

Gaurav

Please see the attached. I have updated the load script and added a new Stock Report table to the Main tab. Is this what you need?

Regards

Jonathan

Re: Help for Task

gaurav2017 — Tue, 26 Jun 2012 11:11:32 GMT

Hi Jonathan,

My company is not having spare CALs, therefore I am using Personal Edition, so pls share the code

Re: Help for Task

Tue, 26 Jun 2012 11:13:34 GMT

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';

Calendar:

Load Dual(Month, RowNo()) As Month, Quarter Inline

[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];

Sales:

Load *, Month(MakeDate(FYear, Period)) As Month Inline

[A, B, C, D, E, F, G, Period, FYear, PrimarySales, SecondarySales

P, Q, R, S, PQ, RST, 1, 07, 2012, 500, 25

P, Q, R, S, QP, TSR, 1, 08, 2012, 100, 30

P, Q, R, S, PP, RRS, 1, 09, 2012, 130, 40

P, Q, R, S, QQ, SST, 1, 10, 2012, 80, 10];

Join(Sales)

LOAD Month,

FYear,

Alt(Peek('ClosingBalance'), 0) As OpeningBalance,

//Peek('ClosingBalance') + Sum(PrimarySales) - Sum(SecondarySales) As ClosingBalance

Alt(Peek('ClosingBalance'), 0) + Sum(PrimarySales) - Sum(SecondarySales) As ClosingBalance

Resident Sales

Group By FYear, Month

Order By FYear, Month;

Re: Help for Task

gaurav2017 — Tue, 26 Jun 2012 11:26:34 GMT

Please Explain the code segments which are color coded:

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';

Calendar:

Load Dual(Month, RowNo()) As Month, Quarter Inline

[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];

Sales:

Load *, Month(MakeDate(FYear, Period)) As Month Inline

[A, B, C, D, E, F, G, Period, FYear, PrimarySales, SecondarySales

P, Q, R, S, PQ, RST, 1, 07, 2012, 500, 25

P, Q, R, S, QP, TSR, 1, 08, 2012, 100, 30

P, Q, R, S, PP, RRS, 1, 09, 2012, 130, 40

P, Q, R, S, QQ, SST, 1, 10, 2012, 80, 10];

Join(Sales)

LOAD Month,

FYear,

Alt(Peek('ClosingBalance'), 0) As OpeningBalance,

//Peek('ClosingBalance') + Sum(PrimarySales) - Sum(SecondarySales) As ClosingBalance

Alt(Peek('ClosingBalance'), 0) + Sum(PrimarySales) - Sum(SecondarySales) As ClosingBalance

Resident Sales

Group By FYear, Month

Order By FYear, Month;

Re: Help for Task

gaurav2017 — Tue, 26 Jun 2012 11:42:32 GMT

A new situation is there now. I have opening Sales for July, 2012.. Now the

"ClosingSales of July" = "OpeningSales of Aug"

"ClosingSales of Aug" = "OpeningSales of Sep"

& so on.

Plus I need

ClosingSales = (OpeningSales+PrimarySales) - SecondarySales

Regards,

Gaurav Malhotra

Re: Help for Task

jonathandienst — Tue, 26 Jun 2012 11:45:03 GMT

I will leave that to Prasath...

Re: Help for Task

gaurav2017 — Tue, 26 Jun 2012 11:46:42 GMT

Jonathan, pls help.

Re: Help for Task

Tue, 26 Jun 2012 11:48:09 GMT

Hi Gaurav,

give me some time i will come with the solution

Regards,

Prasath

Re: Help for Task

gaurav2017 — Tue, 26 Jun 2012 11:50:38 GMT

Hi All,

First Pls explain that code segments.

Regards

Gaurav Malhotra

Re: Help for Task

Tue, 26 Jun 2012 12:00:27 GMT

Hi,

Actually in your case you want to create the calendar with the month in the sorting order because, defalut sorting order will be jan - dec (1-12), but we want to create the sorting order that Jul - June (1-12).

Dual() - this function is used to create the months from 1 - 12, which will be used for sorting order.

Month(Makedate()) - this function is also create the month and year as the same field name.

where the month from the calendar table and sales table will be linked.

Peek() - It is one of the good function which will return the previous row field value, for example Stock(Jul) = 200,

when we use Peek(Stock) = Aug, this will be the July stock will be placed here.

Regards,

Prasath

Re: Help for Task

gaurav2017 — Tue, 26 Jun 2012 12:04:17 GMT

What is Alt() ??

Is it for Alternate value???

Re: Help for Task

Tue, 26 Jun 2012 12:07:32 GMT

Hi,

Alt() is the works as the switch case statments as in programming languages.

alt(case1[ , case2 , case3 , ...] , else)

The alt function returns the first of the parameters that has a valid number representation. If no such match is found, the last parameter will be returned. Any number of parameters can be used.

Example:

alt( date#( dat , 'YYYY/MM/DD' ),

date#( dat , 'MM/DD/YYYY' ),

date#( dat , 'MM/DD/YY' ),

'No valid date' )

Will test if the field date contains a date according to any of the three specified date formats. If so, it will return the original string and a valid number representation of a date. If no match is found, the text 'No valid date' will be returned (without any valid number representation).

Regards

Prasath

Re: Help for Task

gaurav2017 — Tue, 26 Jun 2012 12:31:20 GMT

In order to solve my problem, we should first compute "ClosingSales"

then the ClosingSales for July will become the OpeningSales of Aug

Re: Help for Task

gaurav2017 — Thu, 28 Jun 2012 05:07:42 GMT

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

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

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

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