topic Re: How to get first day of each month price in a column for each dimension in straight table in QlikView

How to get first day of each month price in a column for each dimension in straight table

naveen341 — Fri, 19 Oct 2018 14:10:55 GMT

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

Re: How to get first day of each month price in a column for each dimension in straight table

sunny_talwar — Fri, 19 Oct 2018 14:13:26 GMT

Try this as an expression

FirstSortedValue(TOTAL <Product> Price, Date)

Re: How to get first day of each month price in a column for each dimension in straight table

naveen341 — Fri, 19 Oct 2018 14:26:28 GMT

Hi Sunny

its not working

I tried the expression

only( Aggr( only({$<Date=,Date={'$(=Monthstart(max(Date)))'}>} Price),Date,Product))\

But it gave me an output

Date	Product	Price	First day Price
10/1/2018	A	100	100
10/1/2018	B	500	500
10/1/2018	C	20	20
10/2/2018	A	200
10/2/2018	B	150
10/2/2018	C	30
10/3/2018	A	300
10/3/2018	B	300
10/3/2018	C	80
10/4/2018	A	300
10/4/2018	B	250
10/4/2018	C	110
10/5/2018	A	300
10/5/2018	B	600
10/5/2018	C	60

can you please help me with this

Expected output

Date	Product	Price	First day Price
10/1/2018	A	100	100
10/1/2018	B	500	500
10/1/2018	C	20	20
10/2/2018	A	200	100
10/2/2018	B	150	500
10/2/2018	C	30	20
10/3/2018	A	300	100
10/3/2018	B	300	500
10/3/2018	C	80	20
10/4/2018	A	300	100
10/4/2018	B	250	500
10/4/2018	C	110	20
10/5/2018	A	300	100
10/5/2018	B	600	500
10/5/2018	C	60	20

Re: How to get first day of each month price in a column for each dimension in straight table

sunny_talwar — Fri, 19 Oct 2018 14:34:14 GMT

Worked for me

Can you check your expression one more time. Also, do you have an expression for Price? or Price is a field?

Re: How to get first day of each month price in a column for each dimension in straight table

naveen341 — Fri, 19 Oct 2018 14:36:41 GMT

Yes i do have an expression for price field.

=(avg({<Year=, Quarter=, Month=, Week=, Date={'>=$(=Monthstart(max(Date)))<=$(=max(Date))'}>} Price))

Re: How to get first day of each month price in a column for each dimension in straight table

naveen341 — Fri, 19 Oct 2018 14:38:23 GMT

KeyDate	Index Ticker	=(avg({<Year=, Quarter=, Month=, Week=, KeyDate={'>=10/01/2018<=10/05/2018'}>} PX_Last ))	only( Aggr( only({$<KeyDate=,KeyDate={'10/01/2018'}>} PX_Last ),KeyDate,[Index Ticker]))//=avg( Aggr(avg({$<KeyDate={'$(=Monthstart(max(KeyDate)))'}>} PX_Last ),[Index Ticker],KeyDate))
		644.90873333
10/01/2018	G8QI	375.05500000	375.0550
10/01/2018	JPEIGLBL	778.67000000	778.6700
10/01/2018	JPEIPLUS	794.76000000	794.7600
10/02/2018	G8QI	376.57500000
10/02/2018	JPEIGLBL	778.09000000
10/02/2018	JPEIPLUS	793.74000000
10/03/2018	G8QI	370.21100000
10/03/2018	JPEIGLBL	777.15000000
10/03/2018	JPEIPLUS	792.12000000
10/04/2018	G8QI	367.26100000
10/04/2018	JPEIGLBL	771.11000000
10/04/2018	JPEIPLUS	784.22000000
10/05/2018	G8QI	363.73900000
10/05/2018	JPEIGLBL	769.08000000
10/05/2018	JPEIPLUS	781.85000000

This is my orginal fields and data.

first expression:=(avg({<Year=, Quarter=, Month=, Week=, KeyDate={'>=$(=Monthstart(max(KeyDate)))<=$(=max(KeyDate))'}>} PX_Last ))

second expression: only( Aggr( only({$<KeyDate=,KeyDate={'$(=Monthstart(max(KeyDate)))'}>} PX_Last ),KeyDate,[Index Ticker]))

Re: How to get first day of each month price in a column for each dimension in straight table

sunny_talwar — Fri, 19 Oct 2018 14:57:05 GMT

Try this

FirstSortedValue({<Year, Quarter, Month, Week, Date = {'>=$(=Monthstart(Max(Date)))<=$(=Max(Date))'}>} TOTAL <Product> Aggr(Avg({<Year, Quarter, Month, Week, Date = {'>=$(=Monthstart(Max(Date)))<=$(=Max(Date))'}>} Price), KeyDate, [Index Ticker]), Aggr(Only({<Year, Quarter, Month, Week, Date = {'>=$(=Monthstart(Max(Date)))<=$(=Max(Date))'}>} KeyDate), KeyDate, [Index Ticker]))

Re: How to get first day of each month price in a column for each dimension in straight table

naveen341 — Fri, 19 Oct 2018 15:04:28 GMT

Awesome Sunny,

thanks Much this worked.

Re: How to get first day of each month price in a column for each dimension in straight table

sunny_talwar — Fri, 19 Oct 2018 15:12:45 GMT

Great, please close the thread by marking correct response.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

Re: How to get first day of each month price in a column for each dimension in straight table

naveen341 — Fri, 19 Oct 2018 15:46:02 GMT

HI Sunny

Can you please help me with month year field .

I mean if my dimension is like jan-2018

first expression is :

=avg({<Year=, Quarter=, Month=, Week=, KeyDate={'>=$(=YearStart(max(KeyDate)))<=$(=max(KeyDate))'}>} PX_Last ).

How do i get the month avgerage in all months.

I tried this expression but its not giving the excat o/p

(FirstSortedValue({<Year, Quarter, Month, Week, Date = {'>=$(=YearStart(Max(Date)))<=$(=Max(Date))'}>} TOTAL <[Index Ticker]> Aggr(avg({<Year, Quarter, Month, Week, Date = {'>=$(=YearStart(Max(Date)))<=$(=Max(Date))'}>} PX_Last),

KeyDate, [Index Ticker]), Aggr(avg({<Year, Quarter, Month, Week, Date = {'>=$(=YearStart(Max(Date)))<=$(=Max(Date))'}>} KeyDate), KeyDate, [Index Ticker])))

Re: How to get first day of each month price in a column for each dimension in straight table

sunny_talwar — Fri, 19 Oct 2018 15:50:37 GMT

I am not sure I follow

Re: How to get first day of each month price in a column for each dimension in straight table

naveen341 — Fri, 19 Oct 2018 15:53:19 GMT

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

Re: How to get first day of each month price in a column for each dimension in straight table

sunny_talwar — Fri, 19 Oct 2018 15:58:09 GMT

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])))

Re: How to get first day of each month price in a column for each dimension in straight table

naveen341 — Fri, 19 Oct 2018 17:18:16 GMT

Awesome it work thanks Sunny

Re: How to get first day of each month price in a column for each dimension in straight table

sunny_talwar — Fri, 19 Oct 2018 17:46:05 GMT

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

Re: How to get first day of each month price in a column for each dimension in straight table

naveen341 — Fri, 19 Oct 2018 17:54:35 GMT

It was my bad i hit that button without knowingly.

Your response worked for me.

Re: How to get first day of each month price in a column for each dimension in straight table

sunny_talwar — Fri, 19 Oct 2018 18:00:15 GMT

Then please fix it . If you don't know how... go through the thread below

Qlik Community Tip: Marking Replies as Correct or Helpful