Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
smilingjohn
Specialist
Specialist

Calculated dimesion

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 

Labels (3)
3 Replies
MayilVahanan

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
];

MayilVahanan_0-1655274790151.png

 

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
smilingjohn
Specialist
Specialist
Author

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

MayilVahanan

Hi 

If front end, try like below

 

Dim1: Product

Dim2: =Aggr(If(RangeMax(Phase1, Phase2, Phase3)>=Today(), 'Active', 'Passive'), Product)

MayilVahanan_0-1655277988188.png

 

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.