topic Re: Set Analysis with FirstSortedValue in QlikView

Set Analysis with FirstSortedValue

rcandeo — Wed, 07 Dec 2016 11:41:06 GMT

In this application I wanna show for each Principal and Subcategory, which is my last Visit and the avg of the values of this visit.

The problem is that my expression is not working properly.

In Box 1: Last Visit is the calculation that is not working.

VISIT calculation is showing correctly, but Last Visit is calculating the average for all Visits, and should calculate only the AVG for the Visit that appears on the table. On this example should be 0.

Last Visit 2 is supppose to be the same calculation with the first $ in the right place, but is doesn´t work showing this null value.

Box 2 is only a simple AVG calculation. I expect to see 3 lines, one for each VISIT (SCREENING, DAY 1/RCP, DAY 85/RCP), but is only showing for SCREENING cause the others are 0. I tried to show all values but shows me a lot of null lines and I don´t know why.

Box 3 is the complete data

Anyone can explain me how to fix this Last Visit Calculation?

Mine is avg(${<VISIT={$(=FirstSortedValue(DISTINCT VISIT,-Date))}>} Value)

Re: Set Analysis with FirstSortedValue

vinieme12 — Wed, 07 Dec 2016 11:48:21 GMT

Your Date field is a Text, can't use FirstSortedValue with that, can you post the excel workbook as well

Re: Set Analysis with FirstSortedValue

MK_QSL — Wed, 07 Dec 2016 11:51:51 GMT

May be try this..

Avg({<VISIT = {"$(=FirstSortedValue(DISTINCT VISIT,-Date))"}>}Value)

Re: Set Analysis with FirstSortedValue

MK_QSL — Wed, 07 Dec 2016 11:55:01 GMT

Box 2

Go to presentation tab and untick suppress zero values

Re: Set Analysis with FirstSortedValue

rcandeo — Wed, 07 Dec 2016 14:01:06 GMT

I changed to date and nothing works now. It should work with date or only number?

Re: Set Analysis with FirstSortedValue

vinieme12 — Wed, 07 Dec 2016 14:06:18 GMT

remove the DISTINCT Keyword , i haven't tested this though

Re: Set Analysis with FirstSortedValue

sunny_talwar — Wed, 07 Dec 2016 14:13:18 GMT

May be like attached?

Re: Set Analysis with FirstSortedValue

sunny_talwar — Wed, 07 Dec 2016 14:16:39 GMT

1st Box's expression needs single quotes

Avg({$<VISIT={'$(=FirstSortedValue(DISTINCT VISIT,-Date))'}>} Value)

2nd Box's expression

Avg(Value)+Avg(0.00000000000000000000000001)

and change the number tab to show up until 8 decimals

Re: Set Analysis with FirstSortedValue

vinieme12 — Wed, 07 Dec 2016 14:21:29 GMT

This Also Works

VISIT =MaxString({<Date = {'$(=MIN(Date))'}>} VISIT)

Max({<Date = {'$(=MIN(Date))'}>} Value)

MaxString({<Date = {'$(=MIN(Date))'}>} Date)

AVG({<Date = {'$(=MIN(Date))'}>} Value)

Re: Set Analysis with FirstSortedValue

rcandeo — Wed, 07 Dec 2016 18:19:59 GMT

Sorry but it is not working correct for all cases. See again the image.

Box 1, first line. 250 is the average for All visits in Ambulation, and should be the average for only Visit DAY 85/RCP, that is 407.5

Application attached again

Re: Set Analysis with FirstSortedValue

rcandeo — Wed, 07 Dec 2016 18:22:02 GMT

here is app

Re: Set Analysis with FirstSortedValue

rcandeo — Wed, 07 Dec 2016 18:23:26 GMT

did not work

Re: Set Analysis with FirstSortedValue

sunny_talwar — Wed, 07 Dec 2016 18:49:47 GMT

Try this:

Avg(Aggr(If(FirstSortedValue(TOTAL <Principal, QSSCAT> DISTINCT VISIT, -Date) = VISIT, Avg(Value)), Principal, QSSCAT, VISIT))

Re: Set Analysis with FirstSortedValue

rcandeo — Wed, 07 Dec 2016 19:28:59 GMT

Thank You so Much. This have the maximum difficult nivel.