Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team,
I would like to create an automatic analyses based on groups.
I try to show you with this example:
I have this database where the claims changes the Cost depends on the month and they are different status:
Claim | Date | Status | Cost |
1111 | may-20 | Open | 5000 |
1112 | may-20 | Cosed | 6000 |
1113 | may-20 | Open | 7000 |
1111 | jun-20 | Open | 6000 |
1112 | jun-20 | Re-Open | 7000 |
1113 | jun-20 | Cosed | 8000 |
1111 | jul-20 | Closed | 7000 |
1112 | ago-20 | Open | 8000 |
1113 | sep-20 | Cosed | 8000 |
I would like to show, for example: Show the claims with Status Open in may-20:
Claim | may-20 | jun-20 | jul-20 |
1111 | 5000 | 6000 | 7000 |
1113 | 7000 | 8000 | 8000 |
Could be possible with pivot table and filters (working in set analyses)? I would like it would be dynamic and changing the filters.
Thank you very much.
Hi,
I think you would use Set Analysis & P() function, so start with;
Sum({<Claim=P({<Date={'may-20'},Status={'Open'}>}Claim) >}Cost)
You may need a variable to input your date & have further requirement about what to do when they close, but think that is a start.
Cheers,
Chris.
Hi,
I think you would use Set Analysis & P() function, so start with;
Sum({<Claim=P({<Date={'may-20'},Status={'Open'}>}Claim) >}Cost)
You may need a variable to input your date & have further requirement about what to do when they close, but think that is a start.
Cheers,
Chris.
Hi, thank you very much.
I consider it is the good function but I have a problem with the date:
Sum({<CLAIMS=P({<DT_EXC = {'(01/01/2021)'},STATUS={'1'}>}CLAIMS) >} COST)
Do you know how I could resolve the problem?
Thank you!
Hi,
If your date includes the timestamp then think you are going to need it to & you seem to have some brackets (have you used a variable here?), you want to be getting to;
Sum({<Claim=P({<Date={'01/01/2021 00:00:00'},Status={'Open'}>}Claim) >}Cost)
Unless you need to know if it was open in a month of dates .. in which case do you have a master calendar?
Cheers,
Chris.
Yes, I have to do it.
Thank you very much Chris.