Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
Can you please help me with getting first day of each month price as new column .
Here is the sample data and expected o/p
Sample Data:
Date | Product | Price |
10/1/2018 | A | 100 |
10/2/2018 | A | 200 |
10/3/2018 | A | 300 |
10/4/2018 | A | 300 |
10/5/2018 | A | 300 |
10/1/2018 | B | 500 |
10/2/2018 | B | 150 |
10/3/2018 | B | 300 |
10/4/2018 | B | 250 |
10/5/2018 | B | 600 |
10/1/2018 | C | 20 |
10/2/2018 | C | 30 |
10/3/2018 | C | 80 |
10/4/2018 | C | 110 |
10/5/2018 | C | 60 |
Expected O/P:
Date | Product | Price | First day Price |
10/1/2018 | A | 100 | 100 |
10/2/2018 | A | 200 | 100 |
10/3/2018 | A | 300 | 100 |
10/4/2018 | A | 300 | 100 |
10/5/2018 | A | 300 | 100 |
10/1/2018 | B | 500 | 500 |
10/2/2018 | B | 150 | 500 |
10/3/2018 | B | 300 | 500 |
10/4/2018 | B | 250 | 500 |
10/5/2018 | B | 600 | 500 |
10/1/2018 | C | 20 | 20 |
10/2/2018 | C | 30 | 20 |
10/3/2018 | C | 80 | 20 |
10/4/2018 | C | 110 | 20 |
10/5/2018 | C | 60 | 20 |
Appreciate your help
I am not sure I follow
when i use month year for other chart.
first expression: avg({<Year=, Quarter=, Month=, Week=, KeyDate={'>=$(=YearStart(max(KeyDate)))<=$(=max(KeyDate))'}>} PX_Last )
getting jan 2018 value for complete year
second expression:
(FirstSortedValue({<Year, Quarter, Month, Week, KeyDate = {'>=$(=YearStart(Max(Date)))<=$(=Max(Date))'}>} TOTAL <[Index Ticker]> Aggr(avg({<Year, Quarter, Month, Week, KeyDate = {'>=$(=YearStart(Max(Date)))<=$(=Max(Date))'}>} PX_Last),
KeyDate, [Index Ticker]), Aggr(avg({<Year, Quarter, Month, Week, KeyDate = {'>=$(=YearStart(Max(Date)))<=$(=Max(Date))'}>} KeyDate), KeyDate, [Index Ticker])))
second expression is not giving the exact o/p,
Can you please help me
MonthYear | Index Ticker | monthly | (FirstSortedValue({<Year, Quarter, Month, Week, KeyDate = {'>=01/01/2018<=07/31/2018'}>} TOTAL <[Index Ticker]> Aggr(avg({<Year, Quarter, Month, Week, KeyDate = {'>=01/01/2018<=07/31/2018'}>} PX_Last), KeyDate, [Index Ticker]), Aggr(avg({<Year, Quarter, Month, Week, KeyDate = {'>=01/01/2018<=07/31/2018'}>} KeyDate), KeyDate, [Index Ticker]))) //=avg({<Year=, Quarter=, Month=, Week=, KeyDate={'>=$(=YearStart(max(KeyDate)))<=$(=max(KeyDate))'}>} PX_Last ) |
---|---|---|---|
658.86792105 | |||
Jan 2018 | G8QI | 390.11295652 | 396.07400000 |
Jan 2018 | JPEIGLBL | 807.87869565 | 807.95000000 |
Jan 2018 | JPEIPLUS | 834.06739130 | 836.13000000 |
Feb 2018 | G8QI | 374.65700000 | 396.07400000 |
Feb 2018 | JPEIGLBL | 791.63300000 | 807.95000000 |
Feb 2018 | JPEIPLUS | 815.27950000 | 836.13000000 |
Mar 2018 | G8QI | 376.51672727 | 396.07400000 |
Mar 2018 | JPEIGLBL | 790.13954545 | 807.95000000 |
Mar 2018 | JPEIPLUS | 814.19500000 | 836.13000000 |
Apr 2018 | G8QI | 383.20209524 | 396.07400000 |
Apr 2018 | JPEIGLBL | 790.66190476 | 807.95000000 |
Apr 2018 | JPEIPLUS | 815.77523810 | 836.13000000 |
May 2018 | G8QI | 379.92808696 | 396.07400000 |
May 2018 | JPEIGLBL | 772.81739130 | 807.95000000 |
May 2018 | JPEIPLUS | 794.29304348 | 836.13000000 |
Jun 2018 | G8QI | 385.03952381 | 396.07400000 |
Jun 2018 | JPEIGLBL | 766.36809524 | 807.95000000 |
Jun 2018 | JPEIPLUS | 786.19952381 | 836.13000000 |
Jul 2018 | G8QI | 390.81027273 | 396.07400000 |
Jul 2018 | JPEIGLBL | 777.43409091 | 807.95000000 |
Jul 2018 | JPEIPLUS | 797.31181818 | 836.13000000 |
May be this
FirstSortedValue({<Year, Quarter, Month, Week, KeyDate = {'>=$(=YearStart(Max(Date)))<=$(=Max(Date))'}>} TOTAL <[Index Ticker]> Aggr(avg({<Year, Quarter, Month, Week, KeyDate = {'>=$(=YearStart(Max(Date)))<=$(=Max(Date))'}>} PX_Last),
MonthYearField, [Index Ticker]), Aggr(avg({<Year, Quarter, Month, Week, KeyDate = {'>=$(=YearStart(Max(Date)))<=$(=Max(Date))'}>} MonthYearField), MonthYearField, [Index Ticker])))
Awesome it work thanks Sunny
2 things
1) How is your own response "Awesome it work thanks Sunny" a helpful response
2) How is this a thread "Assumed Answer" if my response did work for you as per point number 1 above
I request you to fix the above two things as it helps future visitors to this thread to look for the right answers.
Best,
Sunny
It was my bad i hit that button without knowingly.
Your response worked for me.
Then please fix it . If you don't know how... go through the thread below