Front end expression

[MindaugasBacius]

Hello,

I do have data like:

LOAD * INLINE [

    Date, Type, Price, ID

    2015-01-01, P, 8, 1

    2015-01-01, F, 10, 1

    2015-01-03, P, 2, 1

    2015-01-03, F, 2, 1

    2015-01-01, P, 8, 2

    2015-01-01, F, 10, 2

    2015-01-03, P, 2, 2

    2015-01-03, F, 2, 2

];

The logic

Field Type stands for Plan (P) and Fact (F).

If F/P > 1.1 than 1 else 0 aggregated according to ID and Date.

The temporary table might look like this:

Date          ID Result

2015-01-01 1   1

2015-01-03 1   0

2015-01-01 2   1

2015-01-03 2   0

My goal is to get pivot table that shows the results like:

Date          Result

2015-01-01 2

2015-01-03 0

Please help my out with this issue. All calculations should be made in front end.

Thank you!

[vinieme12 · Accepted solution]

use below, and uncheck suppress zero values

sum(AGGR(if((sum({<Type={'F'}>} Price)/sum({<Type={'P'}>} Price))>1.1,1,0),Date,ID))

[Frank_Hartmann]

see attached file,

hope this helps