3 Replies Latest reply on Jan 17, 2019 3:08 AM by Mahfooj Khan

Calcultate PnL between two date with the Date in Filtre choice

Hi,

I need help for a calculation field please !

Here is my table o nthe database :

 Date PnL 01/01/2019 1,05 02/01/2019 2,1 03/01/2019 1,7 04/01/2019 1,7 05/01/2019 1,8 06/01/2019 1,9

I need a graphic to calculate PnL between two date ("Diff PnL" on my below table). As the date will be in the filter.

I need you to calcul the two column : PnL at Date Min & PnL at Date Max

If the guy filter Date between : 02/01/2019 to 05/01/2019

 Date Min Date Max PnL at Date Min PnL at Date Max Diff PnL Date PnL WINDOW_MAX(min([Date])) WINDOW_MAX(MAX([Date])) ??? ??? PnL Date Max - PnL Date Min 01/01/2019 1,05 02/01/2019 05/01/2019 2,1 1,8 -0,3 02/01/2019 2,1 02/01/2019 05/01/2019 2,1 1,8 -0,3 03/01/2019 1,7 02/01/2019 05/01/2019 2,1 1,8 -0,3 04/01/2019 1,7 02/01/2019 05/01/2019 2,1 1,8 -0,3 05/01/2019 1,8 02/01/2019 05/01/2019 2,1 1,8 -0,3 06/01/2019 1,9 02/01/2019 05/01/2019 2,1 1,8 -0,3

Thomas.

• 1. Re: Calcultate PnL between two date with the Date in Filtre choice

Hi,

Find my appraoch below,

1. Right click + Drag your date field in filters shelf and select range of date filter option. Put it on context.

2. Now create a calculated field called PnL MAX using below logic

{SUM(IF [Date]={MAX([Date])} THEN [Pn L] END)}

2. Create another calculated field called PnL MIN using below logic

{SUM(IF [Date]={MIN([Date])} THEN [Pn L] END)}

3. Get the DIFF. using above created fields

Workbook attached for your reference. Let us know if this help.

Mahfooj

1 of 1 people found this helpful
• 2. Re: Calcultate PnL between two date with the Date in Filtre choice

Fantastic. Thanks very much. It works.

I don't understand the {}.

It doesn't work without it. But I don't understand why this ??