Front end expression

MindaugasBacius — Mon, 15 May 2017 13:57:01 GMT

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!

Re: Front end expression

vinieme12 — Mon, 15 May 2017 14:17:54 GMT

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 — Mon, 15 May 2017 14:23:00 GMT

see attached file,

hope this helps