Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kunkumnaveen
Specialist
Specialist

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
kunkumnaveen
Specialist
Specialist
Author

Any more suggestion on my requirement ...

PFA sample data file

sunny_talwar

May be this

Dimension

Name

SPrice_Calday

Expression

=Only(Aggr(If(SPrice_Calday = Max(TOTAL <Name>SPrice_Calday), Avg(Sprice_Price)), Name, SPrice_Calday))

kunkumnaveen
Specialist
Specialist
Author

I thought what ever the expression written in table chart will work in bar chart too, the thing is I have used the below expression in bar chart which has a drill down dimension

                                                                Drill Down Dimension  :4_Name------->5_Name

Expression:

                Only(Aggr(If(SPrice_Calday = Max(TOTAL <4_Name>SPrice_Calday), Avg(Sprice_Price)), 4_Name, SPrice_Calday))


OutPut:

So at global level (Before Drill Down to 5_Name) the above expression giving out average price for each 4_Name value dimension which is correct (As below)

Capture.PNG

For LARGE:1301.17 is Avg of  (1272.54+1290.04+1340.92)/3

Capture2.PNG
when I click on Large it will drill down to  further  level ( 5_Name),at this level I will have SINGLE,FLAT values

for SINGLE price has to be=(1272.54+1290.04)/2 and

     FLAT price has to be=(1340.92)/1

But what I am getting is the price is not splitting up when u drill down to next level as shown below

Capture1.PNG

I even tried below expression:

Only(Aggr(If(SPrice_Calday = Max(TOTAL <4_Name,5_Name>SPrice_Calday), Avg(Sprice_Price)),4_Name,5_Name,SPrice_Calday))

From Above expression ,at global level(4_Name) I am getting null values when I drill down down I am getting the split  as below

Level1:

Capture.PNGCapture1.PNG             Level2:

  

                                    Capture1.PNG    

How to make both the levels to work with one expression.?

sunny_talwar

Would it be possible to share an update Excel file where we have those fields to test this out