Calculated Dimesion

[smilingjohn]

Hi All, 

I have a below set of data.  using this I need to show a pivot table in two two tables  one is Active and other is passive. 

In Active table only those products should appear whose Phase date should be equal to today or future date. In Passive table only those product should appear whose phase date is null or less then today . To achieve  this I have used below calculated  dimension  Active  =if(Phase>=Today(),Product)   and Passive =if(isnull(Phase) or Phase<Today(),Product).

DATA:

Product	Phase1	Phase2	Phase3
A	11-06-2022	17-06-2022	20-01-2023
B	13-07-2024
C	25-12-2021		20-01-2025
D	01-01-2020	19-07-2021	02-02-2022

OUTPUT:

ACTIVE					PASSIVE
Product	Phase1	Phase2	Phase3		Product	Phase1	Phase2	Phase3
A		17-06-2022	20-01-2023		A	11-06-2022
B	13-07-2024				B
C			20-01-2025		C

But this is not the right way as the number pf products are displaying in both the tables .......

Passive table should display product only if all the phase date are either past date or all are null  even if  phase 1 and phase 2 are null and phase3 is having past date then it should display . 

Expected output:

Product	Phase1	Phase2	Phase3
D	01-01-2020	19-07-2021	02-02-2022

For Active table if any of phase is having future date  and other phase is having past date or null then still this should be displayed in Active table with same value either null or past date . if you look in my Active table output above the formula making the past date blank , shish should be as it is . 

Can please someone help me on this ? 

Thanks 

[abhijitnalekar · Accepted solution]

Hi @smilingjohn,

I hope you are looking at the output like the same as below

[abhijitnalekar · Accepted solution]

I have manipulated the script as below.

Load
if(
(len(Phase1)=0 or Phase1>=Today())
or (len(Phase2)=0 or Phase2>=Today())
or(len(Phase3)=0 or Phase3>=Today())
,'Active','Passive') as Newdim,
*;
Load
Product,
Date#(Phase1,'DD-MM-YYYY') as Phase1,
Date#(Phase2,'DD-MM-YYYY') as Phase2,
Date#(Phase3,'DD-MM-YYYY') as Phase3;

load * inline
[
Product,Phase1,Phase2,Phase3
A,11-06-2022,17-06-2022,20-01-2023
B,13-07-2024,,
C,25-12-2021,,20-01-2025
D,01-01-2020,19-07-2021,02-02-2022
]

hope this help you