Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
I am trying to count the values based on condition. Below is the scenario. I have a table with group, date and score below
Groups | Date | Score |
groceries | 05_2016 | 2 |
groceries | 06_2016 | 3 |
groceries | 07_2016 | 2 |
groceries | 08_2016 | 3 |
groceries | 09_2016 | 1 |
groceries | 10_2016 | 3 |
Electric | 05_2016 | 3 |
Electric | 06_2016 | 4 |
Electric | 07_2016 | 4 |
Electric | 08_2016 | 4 |
Electric | 09_2016 | 1 |
Electric | 10_2016 | 3 |
05_2016 | 4 | |
06_2016 | 2 | |
07_2016 | 3 | |
08_2016 | 1 | |
09_2016 | 3 | |
10_2016 | 4 | |
Telephone | 05_2016 | 5 |
Telephone | 06_2016 | 2 |
Telephone | 07_2016 | 2 |
Telephone | 08_2016 | 3 |
Telephone | 09_2016 | 4 |
Telephone | 10_2016 | 3 |
Rent | 05_2016 | 2 |
Rent | 06_2016 | 1 |
Rent | 07_2016 | 3 |
Rent | 08_2016 | 4 |
Rent | 09_2016 | 5 |
Rent | 10_2016 | 3 |
Wlan | 05_2016 | 1 |
Wlan | 06_2016 | 3 |
Wlan | 07_2016 | 1 |
Wlan | 08_2016 | 2 |
Wlan | 09_2016 | 3 |
Wlan | 10_2016 | 3 |
when I convert this table into pivot table the value look like this
Groups | 05_2016 | 06_2016 | 07_2016 | 08_2016 | 09_2016 | 10_2016 |
groceries | 2 | 3 | 2 | 3 | 1 | 3 |
Electric | 3 | 4 | 4 | 4 | 1 | 3 |
4 | 2 | 3 | 1 | 3 | 4 | |
Telephone | 5 | 2 | 2 | 3 | 4 | 3 |
Rent | 2 | 1 | 3 | 4 | 5 | 3 |
Wlan | 1 | 3 | 1 | 2 | 3 | 3 |
I would like to count all the possible combinations where I have the combination of 1 in first columns and 3 in the respective column and 2 in first columns and 3 in the next columns. for example : 06_2016 has 1 as rent and 07_2016 as 3 as rent.
I would like to do for all the month dynamically i.e. everymonth when new month data is added it should automatically count the all the possible combination of (1 and 3 ) and (2 and 3) for all the month. Could some one please try to help me to resolve the above issue ? It will be a great help.
Thanks and regards
Iras