Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

naveen341
Contributor

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

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:

  

DateProductPrice
10/1/2018A100
10/2/2018A200
10/3/2018A300
10/4/2018A300
10/5/2018A300
10/1/2018B500
10/2/2018B150
10/3/2018B300
10/4/2018B250
10/5/2018B600
10/1/2018C20
10/2/2018C30
10/3/2018C80
10/4/2018C110
10/5/2018C60

Expected O/P:

   

DateProductPriceFirst day Price
10/1/2018A100100
10/2/2018A200100
10/3/2018A300100
10/4/2018A300100
10/5/2018A300100
10/1/2018B500500
10/2/2018B150500
10/3/2018B300500
10/4/2018B250500
10/5/2018B600500
10/1/2018C2020
10/2/2018C3020
10/3/2018C8020
10/4/2018C11020
10/5/2018C6020

Appreciate your help

16 Replies

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

Try this as an expression

FirstSortedValue(TOTAL <Product> Price, Date)

naveen341
Contributor

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

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

   

DateProductPriceFirst day Price
10/1/2018A100100
10/1/2018B500500
10/1/2018C2020
10/2/2018A200
10/2/2018B150
10/2/2018C30
10/3/2018A300
10/3/2018B300
10/3/2018C80
10/4/2018A300
10/4/2018B250
10/4/2018C110
10/5/2018A300
10/5/2018B600
10/5/2018C60

can you please help me with this

Expected output

   

DateProductPriceFirst day Price
10/1/2018A100100
10/1/2018B500500
10/1/2018C2020
10/2/2018A200100
10/2/2018B150500
10/2/2018C3020
10/3/2018A300100
10/3/2018B300500
10/3/2018C8020
10/4/2018A300100
10/4/2018B250500
10/4/2018C11020
10/5/2018A300100
10/5/2018B600500
10/5/2018C6020

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

Worked for me

Capture.PNG

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

naveen341
Contributor

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

Yes i do have an expression for price field.

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

naveen341
Contributor

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

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/2018G8QI375.05500000375.0550
10/01/2018JPEIGLBL778.67000000778.6700
10/01/2018JPEIPLUS794.76000000794.7600
10/02/2018G8QI376.57500000 
10/02/2018JPEIGLBL778.09000000 
10/02/2018JPEIPLUS793.74000000 
10/03/2018G8QI370.21100000 
10/03/2018JPEIGLBL777.15000000 
10/03/2018JPEIPLUS792.12000000 
10/04/2018G8QI367.26100000 
10/04/2018JPEIGLBL771.11000000 
10/04/2018JPEIPLUS784.22000000 
10/05/2018G8QI363.73900000 
10/05/2018JPEIGLBL769.08000000 
10/05/2018JPEIPLUS781.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

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

naveen341
Contributor

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

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

Great, please close the thread by marking correct response.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

naveen341
Contributor

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

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