topic Re: Picking the most recent entry, FirstSortedValue, Aggr in QlikView

Picking the most recent entry, FirstSortedValue, Aggr

Fri, 04 Nov 2016 09:43:12 GMT

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.

Re: Picking the most recent entry, FirstSortedValue, Aggr

sunny_talwar — Fri, 04 Nov 2016 09:48:09 GMT

Try this:

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

Re: Picking the most recent entry, FirstSortedValue, Aggr

Fri, 04 Nov 2016 09:51:58 GMT

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

Thanks for your suggestion.

Re: Picking the most recent entry, FirstSortedValue, Aggr

sunny_talwar — Fri, 04 Nov 2016 09:55:39 GMT

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

Re: Picking the most recent entry, FirstSortedValue, Aggr

Fri, 04 Nov 2016 11:17:31 GMT

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)

Re: Picking the most recent entry, FirstSortedValue, Aggr

sunny_talwar — Fri, 04 Nov 2016 11:21:35 GMT

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?

Re: Picking the most recent entry, FirstSortedValue, Aggr

Fri, 04 Nov 2016 11:52:02 GMT

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.

Re: Picking the most recent entry, FirstSortedValue, Aggr

sunny_talwar — Fri, 04 Nov 2016 11:59:48 GMT

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

Re: Picking the most recent entry, FirstSortedValue, Aggr

Fri, 04 Nov 2016 13:49:20 GMT

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?

Re: Picking the most recent entry, FirstSortedValue, Aggr

Fri, 04 Nov 2016 22:09:26 GMT

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

Re: Picking the most recent entry, FirstSortedValue, Aggr

Sun, 06 Nov 2016 17:52:35 GMT

Can someone help me out here please?

Thanks

Re: Picking the most recent entry, FirstSortedValue, Aggr

sunny_talwar — Sun, 06 Nov 2016 18:32:25 GMT

What do you expect to see when no selection had been made and what do you expect to see with 2-3 different selections as an example. I am having a somewhat hard time to imagine what you required output is.

Re: Picking the most recent entry, FirstSortedValue, Aggr

Sun, 06 Nov 2016 19:10:01 GMT

Hello Sunny,

The above image has 3 fields INVC__DT,Country and a measure. Country 'US' has data on 19thOct,Aust on 15thOct and Netherlands on 14th Oct as their recent dates.

When i used the below expression, it will always give me 'US' numbers as it has the most recent date when nothing is selectedin any of the filters. But what i actually want is that it should Sum all the most recent dates for every country, 19th for US, 15th for Austr and 14th For Nether and show it in the text box.

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

Re: Picking the most recent entry, FirstSortedValue, Aggr

Tue, 08 Nov 2016 18:15:06 GMT

Hello,

Can someone help me here please?

Thanks

Re: Picking the most recent entry, FirstSortedValue, Aggr

Mon, 21 Nov 2016 17:51:54 GMT

Any suggestions on this?

Thanks.