topic Re: Show only last date data with condition in QlikView

Show only last date data with condition

Thu, 15 Sep 2016 12:57:35 GMT

Hi, please, help, i`m stacked..

I`m trying to create table with all measures for the last day(14/09/16) with condition. I have the table like thew attached. 4 Dimensions -Measure, isAnomally, Date and scor3e. I need to create a table only with the measures and score that on max(date) IsAnomally<>0.

I tried FirstSortedValue(Date,-Date) or FirstSortedValue(Measure,-Date) doesn`t work

if(date=max(date),Measure) doesn`t work

Will be glad to hear your ideas, thanks

Re: Show only last date data with condition

sunny_talwar — Thu, 15 Sep 2016 16:47:31 GMT

May be like this

Dimension

Measure

Expressions

1) FirstSortedValue({<IsAnomally = {'<>0'}>} Score, -Date)

2) Date(Max({<IsAnomally = {'<>0'}>} Date))

Re: Show only last date data with condition

Sun, 18 Sep 2016 08:33:30 GMT

Thanks, but unfortunately, it doesn`t work. The result is the last day for each measure when anomaly was <>0. And what i need is the anomaly status of each measure for the 17/09/2016. Smthg like if this measure has ISanomaly<>0 for the last day in the model, then show the score, if it wasn`t anomaly then hide it. I saw that i can use TOTAL, but it seems that with dates it doesn`t work. See the results of your purpose in the attached.

Re: Show only last date data with condition

vinieme12 — Mon, 19 Sep 2016 12:27:29 GMT

Can you post a sample for expected output, it'll be easier to understand.

Re: Show only last date data with condition

Tue, 20 Sep 2016 12:56:31 GMT

Hi, this is the output that i need

Give me all measures and it`s score with IsAnomally<>0 on the max date (18/09/2016 this day)

Re: Show only last date data with condition

tresB — Tue, 20 Sep 2016 13:03:18 GMT

Try like:

1) FirstSortedValue(Total {<IsAnomally = {'<>0'}>} Score, -Date)

2) Date(Max(Total {<IsAnomally = {'<>0'}>} Date))

Re: Show only last date data with condition

Tue, 20 Sep 2016 15:07:23 GMT

Hi, First One doesn`t work, second one returns correct date, but all measures, not only measures with IsAnomaly<>0.....but, thanks