maybe set analysis on calculated value

[Anonymous]

(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%

-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

[sunny_talwar · Accepted solution]

Try this

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

[Anonymous]

You make it look so simple , thank you very much

[Anonymous]

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

[sunny_talwar]

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

[Anonymous]

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

[sunny_talwar]

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

[Anonymous]

yes if possible

[sunny_talwar]

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;

[Anonymous]

I see, Peek ....

Thank you so much