Picking the most recent entry, FirstSortedValue, Aggr

[#1]

Hello Everyone,

I am trying to pick the most recent entry and display it in a text box. The requirement is to get the most recent Sales for one day, and when none of the filters are selected it should Sum every possible Sales and display it.

For example, when No Country is selected in the Filter, the text Box should have the Sales as 3196+2454.

However when a country is selected, it should display the actual numbers, India-2454 and Aus-3196.

I have attached the sample application here, please give me your suggestions.

Thanks.

[sunny_talwar]

Try this:

=Sum(Aggr(FirstSortedValue(Sales,-DateNum), Ctry))

[#3]

And if i have more dimensions, i just have to include them next to the Ctry in Aggr here?

Thanks for your suggestion.

[sunny_talwar]

Well for the result you are looking to get, I would just add Ctry. But if the requirement is to get something else, then you might have to change the expression

[#5]

Hello Sunny,

I am just pasting a snapshot of my actual dashboard, the Daily Sales, daily margin are the text boxes which needs this expression. And when none of the list boxes from the right are selected , the expressions should calculate the most recent values for every single combination. If any of the filters are selected , Qlikview will take acre of it automatically right?

And i am using this expression for Daily Sales which is not correct because it does not consider every combination's recent date/max date.

Num(Sum({<DateNum={$(=Num(Max(SLS_RPT_TRNS.INVC__DT)))}>}PRC_USD_AMT)

[sunny_talwar]

Well selections will be taken care automatically unless you add a set analysis. The question is you want Max(Date) per what dimension? 1 Dimension or multiple dimensions? In a sense, if you had to show me the max date what all dimensions would you add in your straight table?

[#7]

I would want the most recent date's data for all the dimensions on the right. And i want the text box to display it when the end user opens up the dashboard and before making a selection.

How would i replace the Max function as i understand that the Max(Date) would pick the maximum date irrespective of the other dimensions?

I tried to replace the Max with FirstSortedvalue function but that didnot work for me either.

[sunny_talwar]

Ganesh -

Someone else might be able to help you out better, but with the information you are providing me right now, its all guess work . I did provide a solution for your initial request, either try out different things (playing around with the expression) at your end or I suggest to provide more information.

Best,

Sunny

[#9]

Thanks for your inputs Sunny. i will keep exploring. Will you be able to suggest me something on a similar issue here - Including Dimensions in Set expression?

[#10]

Attaching the sample file here.Please give me your suggestions. Thanks.