populating values

[raadwiptec]

Hi All,

This is my scenario. Please suggest.

basically avgprice is getting calculated by front end expression.. now after 12th month.. 13th to 18th month it should populate the same avgprice as 12 th month until 18th..

secondly these avgprice values should also gets populated from the 12th month avgprice for neutalprice and midstockprice from 13 to 18th

year	month	price	avgprice	neutalprice	midstockprice
2015	18	33	23,91666667	23,91666667	23,91666667
2015	17	33	23,91666667	23,91666667	23,91666667
2015	16	33	23,91666667	23,91666667	23,91666667
2015	15	33	23,91666667	23,91666667	23,91666667
2015	14	33	23,91666667	23,91666667	23,91666667
2015	13	33	23,91666667	23,91666667	23,91666667
2015	12	33	23,91666667	23,91666667	23,91666667
2015	11	31	23,09090909	22	77
2015	10	22	22,3	34	6
2015	9	34	22,33333333	22	5
2015	8	11	20,875	33	4
2015	7	44	22,28571429	11	5
2015	6	77	18,66666667	44	6
2015	5	6	7	77	7
2015	4	5	7,25	88	88
2015	3	7	8	99	7
2015	2	8	8,5	44	65
2015	1	9	9	3	4

[sunny_talwar]

You were going to send me this in an email or something bigger? Front end solution would work?

[raadwiptec]

hi sunny yes I am trying to cover this on the script..probably a bigger note.to your email. but in the mean time can u suggest here as well

[sunny_talwar]

I cannot send you a message until you follow me, so either follow me or send me IM since I have already followed you

[raadwiptec]

hi sunny iam trying to find the option to send you a message ..I don't find anywhere in the website

[sunny_talwar]

This is one solution in the script, but this requires order by statement:

Table:

LOAD year,

    month,

    price

//    avgprice,

//    neutalprice,

//    midstockprice

FROM

[https://community.qlik.com/thread/191475]

(html, codepage is 1252, embedded labels, table is @1);

NewTable:

NoConcatenate

LOAD year as Year,

    month as Month,

    price as Price,

//    [average Price] as [Average Price],

    If(month <= 12,

    If(Peek('Year', -12) = year,

    RangeAvg(price, Peek('Price', -1), Peek('Price', -2), Peek('Price', -3), Peek('Price', -4),

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

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

    If(Peek('Year', -11) = year,

    RangeAvg(price, Peek('Price', -1), Peek('Price', -2), Peek('Price', -3), Peek('Price', -4),

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

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

    If(Peek('Year', -10) = year,

    RangeAvg(price, Peek('Price', -1), Peek('Price', -2), Peek('Price', -3), Peek('Price', -4),

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

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

    If(Peek('Year', -9) = year,

    RangeAvg(price, Peek('Price', -1), Peek('Price', -2), Peek('Price', -3), Peek('Price', -4),

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

      Peek('Price', -9)),

    If(Peek('Year', -8) = year,

    RangeAvg(price, Peek('Price', -1), Peek('Price', -2), Peek('Price', -3), Peek('Price', -4),

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

    If(Peek('Year', -7) = year,

    RangeAvg(price, Peek('Price', -1), Peek('Price', -2), Peek('Price', -3), Peek('Price', -4),

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

    If(Peek('Year', -6) = year,

    RangeAvg(price, Peek('Price', -1), Peek('Price', -2), Peek('Price', -3), Peek('Price', -4),

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

    If(Peek('Year', -5) = year,

    RangeAvg(price, Peek('Price', -1), Peek('Price', -2), Peek('Price', -3), Peek('Price', -4),

      Peek('Price', -5)),

    If(Peek('Year', -4) = year,

    RangeAvg(price, Peek('Price', -1), Peek('Price', -2), Peek('Price', -3), Peek('Price', -4)),

    If(Peek('Year', -3) = year,

    RangeAvg(price, Peek('Price', -1), Peek('Price', -2), Peek('Price', -3)),

    If(Peek('Year', -2) = year,

    RangeAvg(price, Peek('Price', -1), Peek('Price', -2)),

    If(Peek('Year', -1) = year,

    RangeAvg(price, Peek('Price', -1)), price)))))))))))),

    Peek('Average Price')) as [Average Price]

Resident Table

Order By year, month;

DROP Table Table;

[raadwiptec]

hi sunny , I have added u as connection and following you. but not able to send you  a message. if I try to send a message to you..it does not allow me ..anything am I missing here?

[sunny_talwar]

No you are doing it right. I added my email to this post and you should have got it in your email. Can you see it?

[raadwiptec]

I just received this email as Sunny T approved you as a connection

but I do not find any other info..ur email id etc.. even the qlikview inbox nothing exists

[raadwiptec]

Is their any front end solutions