topic Re: stock in QlikView

stock

raadwiptec — Wed, 04 Nov 2015 11:16:52 GMT

hi All,

This is my scenario. Please suggest. I don't have any specific data so far.it is only initial stage and few trial and error checks are going on . I require this on the script not as an expression.

my data to some extent looks like the below.

basicall I need to calculate the average value. I have used an excel to calculate the average

so the calculation should be something like the below

12 price value remains as same

11 is average(12 and 11)

10 is average (12,11, and 10)

09 is average (12,11,10 and 09)

so on

secondlt the calculation should be for 2014 ,20103 until back. .so when I click on the calendar an year and month it should reflect the average also.

year of stock	month of stock	price value	average price value
2015	1	3,20000	3,93333
2015	2	2,10000	4,00000
2015	3	4,20000	4,19000
2015	4	2,50000	4,18889
2015	5	4,40000	4,40000
2015	6	2,70000	4,40000
2015	7	4,20000	4,68333
2015	8	7,50000	4,78000
2015	9	7,40000	4,10000
2015	10	4,30000	3,00000
2015	11	2,20000	2,35000
2015	12	2,50000	2,50000

Re: stock

sunny_talwar — Wed, 04 Nov 2015 11:52:48 GMT

There might be a easier way, but may be this:

Table:

LOAD [year of stock],

[month of stock],

[price value],

[average price value]

FROM

Community_190287.xlsx

(ooxml, embedded labels, table is Sheet1);

NewTable:

NoConcatenate

LOAD [year of stock] as [Year of Stock],

[month of stock] as [Month of Stock],

[price value] as [Price Value],

[average price value] as [Average Price Value],

If(Peek('Year of Stock', -11) = [year of stock],

RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3), Peek('Price Value', -4),

Peek('Price Value', -5), Peek('Price Value', -6), Peek('Price Value', -7), Peek('Price Value', -8),

Peek('Price Value', -9), Peek('Price Value', -10), Peek('Price Value', -11)),

If(Peek('Year of Stock', -10) = [year of stock],

RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3), Peek('Price Value', -4),

Peek('Price Value', -5), Peek('Price Value', -6), Peek('Price Value', -7), Peek('Price Value', -8),

Peek('Price Value', -9), Peek('Price Value', -10)),

If(Peek('Year of Stock', -9) = [year of stock],

RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3), Peek('Price Value', -4),

Peek('Price Value', -5), Peek('Price Value', -6), Peek('Price Value', -7), Peek('Price Value', -8),

Peek('Price Value', -9)),

If(Peek('Year of Stock', -8) = [year of stock],

RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3), Peek('Price Value', -4),

Peek('Price Value', -5), Peek('Price Value', -6), Peek('Price Value', -7), Peek('Price Value', -8)),

If(Peek('Year of Stock', -7) = [year of stock],

RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3), Peek('Price Value', -4),

Peek('Price Value', -5), Peek('Price Value', -6), Peek('Price Value', -7)),

If(Peek('Year of Stock', -6) = [year of stock],

RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3), Peek('Price Value', -4),

Peek('Price Value', -5), Peek('Price Value', -6)),

If(Peek('Year of Stock', -5) = [year of stock],

RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3), Peek('Price Value', -4),

Peek('Price Value', -5)),

If(Peek('Year of Stock', -4) = [year of stock],

RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3), Peek('Price Value', -4)),

If(Peek('Year of Stock', -3) = [year of stock],

RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3)),

If(Peek('Year of Stock', -2) = [year of stock],

RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2)),

If(Peek('Year of Stock', -1) = [year of stock],

RangeAvg([price value], Peek('Price Value', -1)), [price value]))))))))))) as [Average Price Value1]

Resident Table

Order By [year of stock], [month of stock] desc;

DROP Table Table;

Re: stock

Kushal_Chawda — Wed, 04 Nov 2015 13:49:21 GMT

not as good as sunny's solution but this is another way of doing it

Data:

LOAD * Inline [

year of stock, month of stock,Price

2015, 1, 320000

2015, 2, 210000

2015, 3, 420000

2015, 4, 250000

2015, 5, 440000

2015, 6, 270000

2015, 7, 420000

2015, 8, 750000

2015, 9, 740000

2015, 10, 430000

2015, 11, 220000

2015, 12, 250000

2014, 1, 320000

2014, 2, 210000

2014, 3, 420000

2014, 4, 250000

2014, 5, 440000

2014, 6, 270000

2014, 7, 420000

2014, 8, 750000

2014, 9, 740000

2014, 10, 430000

2014, 11, 220000

2014, 12, 250000 ];

Final:

LOAD Distinct [year of stock],

avg(Price) as AvgPrice,

1 as [month of stock]

Resident Data

Group by [year of stock];

LOAD Distinct [year of stock],

avg(Price) as AvgPrice,

2 as [month of stock]

Resident Data

where [month of stock]>=2

Group by [year of stock];

LOAD Distinct [year of stock],

avg(Price) as AvgPrice,

3 as [month of stock]

Resident Data

where [month of stock]>=3

Group by [year of stock];

LOAD Distinct [year of stock],

avg(Price) as AvgPrice,

4 as [month of stock]

Resident Data

where [month of stock]>=4

Group by [year of stock];

LOAD Distinct [year of stock],

avg(Price) as AvgPrice,

5 as [month of stock]

Resident Data

where [month of stock]>=5

Group by [year of stock];

LOAD Distinct [year of stock],

avg(Price) as AvgPrice,

6 as [month of stock]

Resident Data

where [month of stock]>=6

Group by [year of stock];

LOAD Distinct [year of stock],

avg(Price) as AvgPrice,

7 as [month of stock]

Resident Data

where [month of stock]>=7

Group by [year of stock];

LOAD Distinct [year of stock],

avg(Price) as AvgPrice,

8 as [month of stock]

Resident Data

where [month of stock]>=8

Group by [year of stock];

LOAD Distinct [year of stock],

avg(Price) as AvgPrice,

9 as [month of stock]

Resident Data

where [month of stock]>=9

Group by [year of stock];

LOAD Distinct [year of stock],

avg(Price) as AvgPrice,

10 as [month of stock]

Resident Data

where [month of stock]>=10

Group by [year of stock];

LOAD Distinct [year of stock],

avg(Price) as AvgPrice,

11 as [month of stock]

Resident Data

where [month of stock]>=11

Group by [year of stock];

LOAD Distinct [year of stock],

sum(Price) as AvgPrice,

12 as [month of stock]

Resident Data

where [month of stock]=12

Group by [year of stock];

Left Join(Final)

LOAD Distinct [year of stock],

[month of stock],

Price

Resident Data;

DROP Table Data;

Re: stock

raadwiptec — Thu, 05 Nov 2015 08:58:10 GMT

hi sunny,

Iam still testing,

Can you just explain me how this works for example taking one or 2 months in picture

If(Peek('Year of Stock', -2) = [year of stock],

RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2)),

Re: stock

sunny_talwar — Thu, 05 Nov 2015 12:54:41 GMT

If(Peek('Year of Stock', -2) = [year of stock],

RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2)),

In simple words the above is checking if hopping back two period has the same year as current row's year then RangeAvg(CurrentRow'sPrice, PreviousRow'sPrice, and Previous to PreviousRow'sPrice)

Important thing here is to have a sorting order Order By [year of stock], [month of stock] desc;

If we don't sort the table correctly, you will see undesirable results.

HTH

Best,

Sunny

Re: stock

raadwiptec — Thu, 05 Nov 2015 14:49:25 GMT

hi sunny,

what is hopping back 2 period. can u specify an example

Re: stock

sunny_talwar — Thu, 05 Nov 2015 14:56:17 GMT

Here is an example:

year of stock	month of stock	price value	average price value	Peek('Year of Stock', -2)	Avg
2015	1	3,20000	3,93333	-
2015	2	2,10000	4,00000	-
2015	3	4,20000	4,19000	2015	320000, 210000,420000
2015	4	2,50000	4,18889	2015	210000,420000,250000
2015	5	4,40000	4,40000	2015	and so on
2015	6	2,70000	4,40000	2015
2015	7	4,20000	4,68333	2015
2015	8	7,50000	4,78000	2015
2015	9	7,40000	4,10000	2015
2015	10	4,30000	3,00000	2015
2015	11	2,20000	2,35000	2015
2015	12	2,50000	2,50000	2015

But the way the if statement is structured, it will check backwards if the first statement is met, it will never go to the next level

So When this is met ->

If(Peek('Year of Stock', -11) = [year of stock],

RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3), Peek('Price Value', -4),

Peek('Price Value', -5), Peek('Price Value', -6), Peek('Price Value', -7), Peek('Price Value', -8),

Peek('Price Value', -9), Peek('Price Value', -10), Peek('Price Value', -11))

Only for the last row above, you get the average for 12 rows. Next row won't meet this -> Peek('Year of Stock', -11) = [year of stock] and will look for next possible match and will meet the condition here -> Peek('Year of Stock', -10) = [year of stock] and so on..

Does that make sense?

Re: stock

Anonymous — Thu, 05 Nov 2015 15:43:11 GMT

Maybe this solution will help:

Sampledata:

LOAD

Num([year of stock]) as [year of stock],

Num([month of stock]) as [month of stock],

Num([price value]) as [price value]

;

LOAD * inline

[

"year of stock", "month of stock", "price value"

"2015", "1", "3,20000"

"2015", "2", "2,10000"

"2015", "3", "4,20000"

"2015", "4", "2,50000"

"2015", "5", "4,40000"

"2015", "6", "2,70000"

"2015", "7", "4,20000"

"2015", "8", "7,50000"

"2015", "9", "7,40000"

"2015", "10", "4,30000"

"2015", "11", "2,20000"

"2015", "12", "2,50000"

];

MapStock:

Mapping Load

[year of stock] & '-' & [month of stock] as value,

[price value] as value

Resident Sampledata;

Averages:

LOAD

[year of stock],

[month of stock],

[price value],

(alt([price month 1], 0) + alt([price month 2], 0) + alt([price month 3], 0) + alt([price month 4], 0)

+ alt([price month 5], 0) + alt([price month 6], 0) + alt([price month 7], 0) + alt([price month 8], 0)

+ alt([price month 9], 0) + alt([price month 10], 0) + alt([price month 11], 0) + alt([price month 12], 0))

/ (if(IsNull([price month 1]), 0, 1) + if(IsNull([price month 2]), 0, 1) + if(IsNull([price month 3]), 0, 1)

+ if(IsNull([price month 4]), 0, 1) + if(IsNull([price month 5]), 0, 1) + if(IsNull([price month 6]), 0, 1)

+ if(IsNull([price month 7]), 0, 1) + if(IsNull([price month 8]), 0, 1) + if(IsNull([price month 9]), 0, 1)

+ if(IsNull([price month 10]), 0, 1) + if(IsNull([price month 11]), 0, 1) + if(IsNull([price month 12]), 0, 1))

as [average price value]

;

LOAD

[year of stock],

[month of stock],

[price value],

if([month of stock] <= 1, ApplyMap('MapStock', [year of stock] & '-' & 1, Null()), Null()) as [price month 1],

if([month of stock] <= 2, ApplyMap('MapStock', [year of stock] & '-' & 2, Null()), Null()) as [price month 2],

if([month of stock] <= 3, ApplyMap('MapStock', [year of stock] & '-' & 3, Null()), Null()) as [price month 3],

if([month of stock] <= 4, ApplyMap('MapStock', [year of stock] & '-' & 4, Null()), Null()) as [price month 4],

if([month of stock] <= 5, ApplyMap('MapStock', [year of stock] & '-' & 5, Null()), Null()) as [price month 5],

if([month of stock] <= 6, ApplyMap('MapStock', [year of stock] & '-' & 6, Null()), Null()) as [price month 6],

if([month of stock] <= 7, ApplyMap('MapStock', [year of stock] & '-' & 7, Null()), Null()) as [price month 7],

if([month of stock] <= 8, ApplyMap('MapStock', [year of stock] & '-' & 8, Null()), Null()) as [price month 8],

if([month of stock] <= 9, ApplyMap('MapStock', [year of stock] & '-' & 9, Null()), Null()) as [price month 9],

if([month of stock] <= 10, ApplyMap('MapStock', [year of stock] & '-' & 10, Null()), Null()) as [price month 10],

if([month of stock] <= 11, ApplyMap('MapStock', [year of stock] & '-' & 11, Null()), Null()) as [price month 11],

if([month of stock] <= 12, ApplyMap('MapStock', [year of stock] & '-' & 12, Null()), Null()) as [price month 12]

Resident Sampledata;

;

Drop Table Sampledata;

Re: stock

raadwiptec — Thu, 05 Nov 2015 16:37:31 GMT

hi sunny,

I have one more problem I have 13 th and 14th month

Re: stock

sunny_talwar — Thu, 05 Nov 2015 16:38:31 GMT

In a year you have 13th and 14th month?

Re: stock

raadwiptec — Fri, 06 Nov 2015 07:43:13 GMT

hi sunny,

this is an financial arrangement ..some companies can have until 18th month.. for many adjustments

Re: stock

sunny_talwar — Fri, 06 Nov 2015 09:01:06 GMT

For 18 months (year being still the same) you can try this code:

Table:

LOAD [year of stock],

[month of stock],

[price value],

[average price value]

FROM

Community_190287.xlsx

(ooxml, embedded labels, table is Sheet1);

NewTable:

NoConcatenate

LOAD [year of stock] as [Year of Stock],

[month of stock] as [Month of Stock],

[price value] as [Price Value],

[average price value] as [Average Price Value],

If(Peek('Year of Stock', -17) = [year of stock],

RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3), Peek('Price Value', -4),

Peek('Price Value', -5), Peek('Price Value', -6), Peek('Price Value', -7), Peek('Price Value', -8),

Peek('Price Value', -9), Peek('Price Value', -10), Peek('Price Value', -11), Peek('Price Value', -12),

Peek('Price Value', -13), Peek('Price Value', -14), Peek('Price Value', -15), Peek('Price Value', -16),

Peek('Price Value', -17)),

If(Peek('Year of Stock', -16) = [year of stock],

RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3), Peek('Price Value', -4),

Peek('Price Value', -5), Peek('Price Value', -6), Peek('Price Value', -7), Peek('Price Value', -8),

Peek('Price Value', -9), Peek('Price Value', -10), Peek('Price Value', -11), Peek('Price Value', -12),

Peek('Price Value', -13), Peek('Price Value', -14), Peek('Price Value', -15), Peek('Price Value', -16)),

If(Peek('Year of Stock', -15) = [year of stock],

RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3), Peek('Price Value', -4),

Peek('Price Value', -5), Peek('Price Value', -6), Peek('Price Value', -7), Peek('Price Value', -8),

Peek('Price Value', -9), Peek('Price Value', -10), Peek('Price Value', -11), Peek('Price Value', -12),

Peek('Price Value', -13), Peek('Price Value', -14), Peek('Price Value', -15)),

If(Peek('Year of Stock', -14) = [year of stock],

RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3), Peek('Price Value', -4),

Peek('Price Value', -5), Peek('Price Value', -6), Peek('Price Value', -7), Peek('Price Value', -8),

Peek('Price Value', -9), Peek('Price Value', -10), Peek('Price Value', -11), Peek('Price Value', -12),

Peek('Price Value', -13), Peek('Price Value', -14)),

If(Peek('Year of Stock', -13) = [year of stock],

RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3), Peek('Price Value', -4),

Peek('Price Value', -5), Peek('Price Value', -6), Peek('Price Value', -7), Peek('Price Value', -8),

Peek('Price Value', -9), Peek('Price Value', -10), Peek('Price Value', -11), Peek('Price Value', -12),

Peek('Price Value', -13)),

If(Peek('Year of Stock', -12) = [year of stock],

RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3), Peek('Price Value', -4),

Peek('Price Value', -5), Peek('Price Value', -6), Peek('Price Value', -7), Peek('Price Value', -8),

Peek('Price Value', -9), Peek('Price Value', -10), Peek('Price Value', -11), Peek('Price Value', -12)),

If(Peek('Year of Stock', -11) = [year of stock],

RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3), Peek('Price Value', -4),

Peek('Price Value', -5), Peek('Price Value', -6), Peek('Price Value', -7), Peek('Price Value', -8),

Peek('Price Value', -9), Peek('Price Value', -10), Peek('Price Value', -11)),

If(Peek('Year of Stock', -10) = [year of stock],

RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3), Peek('Price Value', -4),

Peek('Price Value', -5), Peek('Price Value', -6), Peek('Price Value', -7), Peek('Price Value', -8),

Peek('Price Value', -9), Peek('Price Value', -10)),

If(Peek('Year of Stock', -9) = [year of stock],

RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3), Peek('Price Value', -4),

Peek('Price Value', -5), Peek('Price Value', -6), Peek('Price Value', -7), Peek('Price Value', -8),

Peek('Price Value', -9)),

If(Peek('Year of Stock', -8) = [year of stock],

RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3), Peek('Price Value', -4),

Peek('Price Value', -5), Peek('Price Value', -6), Peek('Price Value', -7), Peek('Price Value', -8)),

If(Peek('Year of Stock', -7) = [year of stock],

RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3), Peek('Price Value', -4),

Peek('Price Value', -5), Peek('Price Value', -6), Peek('Price Value', -7)),

If(Peek('Year of Stock', -6) = [year of stock],

RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3), Peek('Price Value', -4),

Peek('Price Value', -5), Peek('Price Value', -6)),

If(Peek('Year of Stock', -5) = [year of stock],

RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3), Peek('Price Value', -4),

Peek('Price Value', -5)),

If(Peek('Year of Stock', -4) = [year of stock],

RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3), Peek('Price Value', -4)),

If(Peek('Year of Stock', -3) = [year of stock],

RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2), Peek('Price Value', -3)),

If(Peek('Year of Stock', -2) = [year of stock],

RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2)),

If(Peek('Year of Stock', -1) = [year of stock],

RangeAvg([price value], Peek('Price Value', -1)), [price value]))))))))))))))))) as [Average Price Value1]

Resident Table

Order By [year of stock], [month of stock] desc;

DROP Table Table;

If year can change, please provide a representative sample of how your data might look.

Best,

Sunny

Re: stock

raadwiptec — Fri, 13 Nov 2015 08:48:20 GMT

Hi Sunny,

It looks good now. I need a small change here. the calculation should be reversed..

that means

1st month - the same value

2nd month - avg of 1 and 2

3rd - avg of 1,2,3

4th ...

but certain calculations I feel its not taking average. or I am wrong?

Re: stock

sunny_talwar — Fri, 13 Nov 2015 11:28:06 GMT

Do you see any place where they are wrong?

For reversing the order of Avg, you can remove the desc from the Order By Statement:

If(Peek('Year of Stock', -2) = [year of stock],

RangeAvg([price value], Peek('Price Value', -1), Peek('Price Value', -2)),

If(Peek('Year of Stock', -1) = [year of stock],

RangeAvg([price value], Peek('Price Value', -1)), [price value]))))))))))))))))) as [Average Price Value1]

Resident Table

Order By [year of stock], [month of stock] desc;

DROP Table Table;

Re: stock

raadwiptec — Fri, 13 Nov 2015 12:21:06 GMT

hi sunny,

Iam trying ..

is their another way to write this script witout using order by clause as iam trying to insert these lines between one long existing code ..just for the sake to get this additional column ...

Re: stock

raadwiptec — Fri, 13 Nov 2015 12:33:15 GMT

hi sunny,

can u provide me the script similar for reverse..?

Re: stock

sunny_talwar — Fri, 13 Nov 2015 12:45:47 GMT

I did in my previous post. Did you look at the attachment?

Re: stock

sunny_talwar — Fri, 13 Nov 2015 12:46:49 GMT

If you don't want to touch the script, why don't you do it on the front end of the application? You can get a much more simpler expression if you do this on the front end of the application.

Re: stock

raadwiptec — Fri, 13 Nov 2015 13:10:35 GMT

hi sunny,

Basically iam facing 2 issues ..your script results is good from calculating from the desc..without using order by clause

if I use order by clause iam getting undesired results.

so I was asking you the script similar one to calculate ascending without using the order by options only for 12 months.. 18 months is no more required..

Can you let me know from the front end also? I prefer this is as last option ..

Re: stock

sunny_talwar — Fri, 13 Nov 2015 13:29:16 GMT

Unfortunately, it won't work without the order by. At least as per my knowledge, and I would love to be proven wrong. Front end expression would be:

=If(Sum([Price Value]) > 0, RangeAvg(Above(Sum({<[Month of Stock] = >}[Price Value]), 0, RowNo())))