Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

peter
New Contributor

Getting firstsortedvalue from spreadsheet with multiple entries per date

Hi, 

I hope someone will be able to help me, I have been struggeling and googling for a couple of weeks on and off. And I am quite sure it should be possible to fix my issue - I just don't know how.

I have a spreadsheet of multiple dates and data entries - see attached image. Each date has several entires. I need to be able to display the latest date in the spreadsheet along with the summed value of each column (for instance ZBB 3 column what I need to display in a text box is "19-12-2018 00:00:00 ZBB 3 = 1" )

I have tried practically all combinations of aggr and sum (except for the right one) 🙂

Can anyone please help?

BR

6 Replies

Re: Getting firstsortedvalue from spreadsheet with multiple entries per date

Is ZBB 3  a field or is it an expression?

peter
New Contributor

Re: Getting firstsortedvalue from spreadsheet with multiple entries per date

it is a field

Re: Getting firstsortedvalue from spreadsheet with multiple entries per date

Try this

FirstSortedValue({<[ZBB 3] = {"*"}>} [ZBB 3], -Date)
peter
New Contributor

Re: Getting firstsortedvalue from spreadsheet with multiple entries per date

As suggested I tried this:

FirstSortedValue({<[@1.ZBB 3] = {*}>} [@1.ZBB 3],-[@1.Date])

Then I thought maybe the date is seen as a string and tried:

FirstSortedValue({<[@1.ZBB 3] = {*}>} [@1.ZBB 3],-Date(Date#([@1.Date],'DD-MM-YYYY  hh:mm:tt')))

 

But none of these ends of in my textbox, it is just blank

Re: Getting firstsortedvalue from spreadsheet with multiple entries per date

How about if you add DISTINCT

FirstSortedValue(DISTINCT {<[@1.ZBB 3] = {*}>} [@1.ZBB 3],-[@1.Date])
ptl14495
New Contributor III

Re: Getting firstsortedvalue from spreadsheet with multiple entries per date

Hi,

If the last date has empty results, we will get last date with null results.

Can you retrieve the results on the last data with results diferent from zero?

thank you