Hello Everyone,
I am struggling with set analysis and showing certain information in a table.
My Table looks like this and I have a table for several dates. So this table for 24.10 and 21.10. I loaded them using the load table*.
|
Customer Number
|
WnoteCode(Customer Rating) |
Datum |
|
1
|
2 |
24.10.2022 |
| 2 |
3 |
24.10.2022 |
| 3 |
5 |
24.10.2022 |
| 4 |
4 |
24.10.2022 |
| 5 |
1 |
24.10.2022 |
| 6 |
1 |
24.10.2022 |
The Problem is I want to show a table in which I calculate the change of customer rating from the day before. But I only want to show if the rating increases. I was already able to use set analysis to only show by how much the customer rating changed but not exclude those that did not change.
It would be so easy to do this if I could calculate the difference in a dimension and remove all the zero values but that did not work. I also tried to remove the zeros in the Add-Ons which worked but as soon as I added another dimension or kpi it didn't anymore. If I ad the new dimension with an if statment that it should be zero as well if the rating did not change it does not work.
Some iterations looked like this:
=if(Max({<Datum={'$(=Date(max([Datum])))'}>} WNoteCode)>=Max({<Datum={'$(=Date(max([Datum],2)))'}>} WNoteCode),Max({<Datum={'$(=Date(max([Datum])))'}>}WNoteCode))
This worked as long as I do not add another dimension or Measure.
=IF(Min({<Datum={'$(=Date(max([Datum])))'}>} WNoteCode)<=Min({<Datum={'$(=Date(max([Datum],2)))'}>} WNoteCode),Null()
,Min({<Datum={'$(=Date(max([Datum])))'}>} WNoteCode)-Min({<Datum={'$(=Date(max([Datum],2)))'}>} WNoteCode))
The Result should look like this:
|
Customer Number
|
WnoteCode(Customer Rating) most recent date |
Change in Customer Rating |
old customer rating |
|
1
|
4 |
2 |
2 |
| 5 |
5 |
4 |
1 |
