Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 | |||||||
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
Hi
Try like below
Load *, If(Phase >= Today(), 'Active', 'Passive') as Flag;
LOAD *, Date(RangeMax(Phase1, Phase2, Phase3)) as Phase 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
];
Thanks for the reply @MayilVahanan
I have write the logic in the calculated dimension so can you please me with the expression and I also I will be using two se[rate tables for show Active Products and Passive Products .
Thanks
Hi
If front end, try like below
Dim1: Product
Dim2: =Aggr(If(RangeMax(Phase1, Phase2, Phase3)>=Today(), 'Active', 'Passive'), Product)