topic Re: maybe set analysis on calculated value in QlikView

maybe set analysis on calculated value

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

(Thanks to Sunny) I now know how to compare grades from one day with grades from 3 days before - attached

Aggr(Sum(Grades)/Above(Sum(Grades), 3)-1, Students, Dates)

now I am looking to display in a text object the average on negative moves only- so average of

-90%

-100%

-96%

-90%

-80%

so -91.04%

I see 2 possible solutions here I am struggling with both

1) set analysis on the average calculated value of negative only... don't think qlik can do that

2) move the "% move from 3 ago" calculation to the load script then I can use an easy set analysis to get average on negative only.

Any idea?

Thank you.

Raphael

Re: maybe set analysis on calculated value

sunny_talwar — Thu, 12 Apr 2018 11:53:16 GMT

Try this

=Num(Avg(Aggr(If(Sum(Grades)/Above(Sum(Grades), 3)-1 < 0, Sum(Grades)/Above(Sum(Grades), 3)-1), Students, Dates)), '##.##%')

Re: maybe set analysis on calculated value

Anonymous — Thu, 12 Apr 2018 11:55:51 GMT

You make it look so simple , thank you very much

Re: maybe set analysis on calculated value

Anonymous — Thu, 12 Apr 2018 12:09:28 GMT

Sunny, out of interest , is it much complicated to move calculation to loading script (my option 2)

Re: maybe set analysis on calculated value

sunny_talwar — Thu, 12 Apr 2018 12:12:04 GMT

Might not be too complicated, but it will become static and won't change based on selections.... it will be fixed...

Re: maybe set analysis on calculated value

Anonymous — Thu, 12 Apr 2018 12:17:58 GMT

(fix is ok now), I just was trying before and could not - think above is not accepted in script.

Re: maybe set analysis on calculated value

sunny_talwar — Thu, 12 Apr 2018 12:20:59 GMT

So, you do want a back end based solution for this?

Re: maybe set analysis on calculated value

Anonymous — Thu, 12 Apr 2018 12:21:58 GMT

yes if possible

Re: maybe set analysis on calculated value

sunny_talwar — Thu, 12 Apr 2018 12:27:26 GMT

Like this

Table:

LOAD * INLINE [

Dates, Students, Grades, F4

01/01/2017, Dan, 10,

01/01/2017, Kim, 5,

01/01/2017, Stephane, 1,

01/01/2017, John, 0,

02/02/2017, Dan, 90,

02/02/2017, Kim, 50,

02/02/2017, Stephane, 20,

02/02/2017, John, 0,

03/03/2017, Dan, 80,

03/03/2017, Kim, 50,

03/03/2017, Stephane, 30,

03/03/2017, John, 0,

04/04/2017, Dan, 100,

04/04/2017, Kim, 50,

04/04/2017, Stephane, 40,

04/04/2017, John, 0,

05/05/2017, Dan, 90,

05/05/2017, Kim, 100,

05/05/2017, Stephane, 50,

05/05/2017, John, 0,

06/10/2017, Dan, 3,

06/10/2017, Kim, 5,

06/10/2017, Stephane,6,

06/10/2017, John, 3,

07/11/2017, Dan, 100,

07/11/2017, Kim, 100,

07/11/2017, Stephane, 70,

07/11/2017, John, 0,

08/11/2017, Dan, 90,

08/12/2017, Kim, 100,

08/12/2017, Stephane, 80,

08/12/2017, John, 0,

09/12/2017, Dan, 80,

09/12/2017, Kim, 100,

09/12/2017, Stephane, 40,

09/12/2017, John, 0,

18/12/2017, Dan, 10,

18/12/2017, Kim, 10,

18/12/2017, Stephane, 100,

18/12/2017, John, 0,

, , ,

];

TempTable:

LOAD *,

If(Students = Peek('Students', -3), Peek('Grades', -3)) as Above3Grade,

Grades/If(Students = Peek('Students', -3), Peek('Grades', -3)) - 1 as Calc1

Resident Table

Order By Students, Dates;

AggregatedValue:

LOAD Avg(Calc1) as Calc2

Resident TempTable

Where Calc1 < 0;

DROP Table Table;

Re: maybe set analysis on calculated value

Anonymous — Thu, 12 Apr 2018 12:28:47 GMT

I see, Peek ....

Thank you so much

Re: maybe set analysis on calculated value

sunny_talwar — Thu, 12 Apr 2018 12:29:54 GMT

Yes, Peek/Previous are the script equivalent of Above