Special Cohort analyses

[lomi89]

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.

[chrismarlow · Accepted solution]

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. 

[lomi89]

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!

[chrismarlow]

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.

[lomi89]

Yes, I have to do it.

Thank you very much Chris.