Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

kunkumnaveen
Contributor III

Max date wise price and name

Hello All,

              I am trying to show Each persons latest price based up on calendar dates ,so to achieve requirement I had return the below expression

                        avg({<SCOPE={'YES'},SPrice_Calday={'$(=max(SPrice_Calday))'}>}Sprice_Price)


but for some reason it is randomly picking only one max date which is 01-10-2017 and showing only those name and price ,and the rest of name it is considering under null, what I mean is


It is showing only those name and price whose max date is 01-10-2017

Capture.PNG

Consider under null for below names whose max date is 31-03-2017

Capture1.PNG

I am trying to show in a straight table

                                              Dimension: 1.Name

                                                                  2)Sprice_Calday

                                                 measure :

                                                            1)  avg({<SCOPE={'YES'},SPrice_Calday={'$(=max(SPrice_Calday))'}>}Sprice_Price)


What am I missing in my expression


Thanks

13 Replies

Re: Max date wise price and name

What are you getting when you use max(SPrice_Calday) ​in text box?

karthikoffi27se
Contributor III

Re: Max date wise price and name

Hi Naveen,

Please use the below expression

=Only(If(Aggr(NODISTINCT MAX(SPrice_Calday), Name) = SPrice_Calday, avg({<SCOPE={'YES'}>}Sprice_Price)))

Many Thanks

Karthik

kunkumnaveen
Contributor III

Re: Max date wise price and name

I am getting  01-10-2017 in KPI

kunkumnaveen
Contributor III

Re: Max date wise price and name

I am getting null values ,when I use above expression

Re: Max date wise price and name

Try this?

Avg({<SCOPE={'YES'},SPrice_Calday={'$(=max(SPrice_Calday))'}>} TOTAL <Name> Sprice_Price)

OR

Aggr(Avg({<SCOPE={'YES'},SPrice_Calday={'$(=max(SPrice_Calday))'}>} Sprice_Price), SPrice_Calday, Name)

karthikoffi27se
Contributor III

Re: Max date wise price and name

Hi Naveen,

Please refer this post

Sum Value of max date per ID

Many Thanks

Karthik

kunkumnaveen
Contributor III

Re: Max date wise price and name

I tried both ur expressions, still it is showing 01-10-2017 date price

kunkumnaveen
Contributor III

Re: Max date wise price and name

Thanks for your effort ,but still I am in unable  to achieve it ,I am attaching sample data

kunkumnaveen
Contributor III

Re: Max date wise price and name

Hi, i  am attaching sample data ,which might give a clear picture of my requirement

Community Browser