Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Picking the most recent entry, FirstSortedValue, Aggr

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.

Most Recent Sales1.jpgFor 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.

Tags (1)
14 Replies
Highlighted

Re: Picking the most recent entry, FirstSortedValue, Aggr

Try this:

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

Highlighted
Not applicable

Re: Picking the most recent entry, FirstSortedValue, Aggr

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

Thanks for your suggestion.

Highlighted

Re: Picking the most recent entry, FirstSortedValue, Aggr

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

Highlighted
Not applicable

Re: Picking the most recent entry, FirstSortedValue, Aggr

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)

Most Recent Sales2.jpg

Highlighted

Re: Picking the most recent entry, FirstSortedValue, Aggr

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?

Highlighted
Not applicable

Re: Picking the most recent entry, FirstSortedValue, Aggr

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.

Highlighted

Re: Picking the most recent entry, FirstSortedValue, Aggr

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

Highlighted
Not applicable

Re: Picking the most recent entry, FirstSortedValue, Aggr

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?

Highlighted
Not applicable

Re: Picking the most recent entry, FirstSortedValue, Aggr

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