Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
You were going to send me this in an email or something bigger? Front end solution would work?
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
I cannot send you a message until you follow me, so either follow me or send me IM since I have already followed you
hi sunny iam trying to find the option to send you a message ..I don't find anywhere in the website
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;
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?
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?
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
Is their any front end solutions