Hi Folks!! Hope you guys are well and fine!!
I have to implement something like below.
Source Table :
| Project | Type | Year | Channel | Amount |
| P-1 | Credit | 2017 | 1 | 20 |
| P-2 | Credit | 2017 | 1 | 30 |
| P-3 | Credit | 2017 | 0 | 200 |
| P-4 | Credit | 2017 | 0 | 300 |
| P-1 | Credit | 2018 | 1 | 20 |
| P-2 | Credit | 2018 | 1 | 30 |
| P-3 | Credit | 2018 | 1 | 10 |
| P-1 | Debit | 2017 | 1 | 20 |
| P-2 | Debit | 2017 | 1 | 30 |
| P-3 | Debit | 2017 | 0 | 40 |
| P-4 | Debit | 2017 | 0 | 50 |
| P-1 | Debit | 2018 | 1 | 60 |
| P-2 | Debit | 2018 | 1 | 70 |
| P-3 | Debit | 2018 | 1 | 80 |
Output (Matrix Visual) :
| Type | 2017 | 2018 |
| Credit | 550 | 60 |
| Debit | 140 | 210 |
I.e. output matrix simple summarizing the Amount based on Type and Year
Till here its fine, however my requirement is is the distinct count of Project for type and any given year is less than 3, then summation of Amount in output will be shown as Blank, e.g. as below
Requirement is detailed below :
Let say my source table having data as shown below
| Project | Type | Year | Channel | Amount |
| P-1 | Credit | 2017 | 1 | 20 |
| P-2 | Credit | 2017 | 1 | 30 |
| | | | | |
| | | | | |
| P-1 | Credit | 2018 | 1 | 20 |
| P-2 | Credit | 2018 | 1 | 30 |
| P-3 | Credit | 2018 | 1 | 10 |
| P-1 | Debit | 2017 | 1 | 20 |
| P-2 | Debit | 2017 | 1 | 30 |
| P-3 | Debit | 2017 | 0 | 40 |
| P-4 | Debit | 2017 | 0 | 50 |
| P-1 | Debit | 2018 | 1 | 60 |
| P-2 | Debit | 2018 | 1 | 70 |
| P-3 | Debit | 2018 | 1 | 80 |
I.e. I dont have data for P-3 and P-4 for Type=Credit and Year =2017, then my output will be shown as below
| Type | 2017 | 2018 |
| Credit | | 60 |
| Debit | 140 | 210 |
Just to explain the output, as there is Only Two project left after removal of P-3 and P-4 for Type=Credit and Year=2017, i.e. number of project is less than 2 for Type=Credit and Year=2017, hence summation of Amount for available project (less than 3) will be shown as BLANK.
Other variation could be, I have filter based on Channel (thats holds values as 1 and 0), lets say user selected Channel=1 then source data will be reduced to as shown below
| Project | Type | Year | Channel | Amount |
| P-1 | Credit | 2017 | 1 | 20 |
| P-2 | Credit | 2017 | 1 | 30 |
| | | | | |
| | | | | |
| P-1 | Credit | 2018 | 1 | 20 |
| P-2 | Credit | 2018 | 1 | 30 |
| P-3 | Credit | 2018 | 1 | 10 |
| P-1 | Debit | 2017 | 1 | 20 |
| P-2 | Debit | 2017 | 1 | 30 |
| | | | | |
| | | | | |
| P-1 | Debit | 2018 | 1 | 60 |
| P-2 | Debit | 2018 | 1 | 70 |
| P-3 | Debit | 2018 | 1 | 80 |
in such a scenario, we left (highliheted in BOLD in source data) with only 2 distinct project for Type=Credit & Year=2017, also for Type=Debit and Year=2017, hence output will be as below:
| Type | 2017 | 2018 |
| Credit | | 60 |
| Debit | | 210 |
In a nutshell, after applying all the filters, I need to implement hiding based on number of distinct Project available, if Number of Distinct Project for any combination (Type and Year) is less than 3, then I want to dispaly summation of Amount as BLANK.
Please suggest!!
Thanks
Amit
