Count of only distinct values from 3 formulas

[divya_anand]

Can someone please help me with this issue?

Formula 1:

(Count({<Year=,Month=,Date=, Day=,TYPE={'A','B'},STATUS-={'CANCEL','REJECTED'},

COMPLETED = {'>=$(=num(MonthStart(Max(Date1))))<$(=(Max(Date1)+1))'} >} distinct TICKETID)

Formula 2:

Count({<Year=,Month=,Date=, Day=,TYPE={'A','B'},STATUS-={'CANCEL','REJECTED'}, RATESTATUS={'R'},

ASSIGNED = {'<$(=(Max(Date1)+1))'} >} distinct TICKETID)

Formula 3:

Count({<Year=,Month=,Date=, Day=,TYPE={'A','B'},STATUS-={'CANCEL','REJECTED'},

ASSIGNED = {'>=$(=num(MonthStart(Max(Date1))))<$(=(Max(Date1)+1))'}, COMPLETED -= {'<$(=num(MonthStart(Max(Date1))))'} >} distinct TICKETID) )

The output that I am expecting is a count of (Formula 1+Formula 2+Formula 3). However there could be situations where the TICKETID could be repeated in more than 1 Formula. And if I take a count as shown, then the duplicates are also considered in the count. Is it possible to get a count of distinct TICKETIDs only ?  

I am using this expression (Formula 1+Formula 2+Formula 3) in a bar chart.

For eg:

The value that I would like to see on the bar chart is "9" & not "11".

Any other information that is needed, Please let me know.

Thank you.

[Anonymous · Accepted solution]

Hi,

From your post i understand that F3 is not showing correctly,please change like this:

Count({<Year=,Month=,Date=, Day=,TYPE={'A','B'},STATUS-={'CANCEL','REJECTED'},

COMPLETED = {'>=$(=num(MonthStart(Max(Date1))))<$(=(Max(Date1)+1))'}

+

<Year=,Month=,Date=, Day=,TYPE={'A','B'},STATUS-={'CANCEL','REJECTED'}, RATESTATUS={'R'},

ASSIGNED = {'<$(=(Max(Date1)+1))'} >

+

<Year=,Month=,Date=, Day=,TYPE={'A','B'},STATUS-={'CANCEL','REJECTED'},

ASSIGNED = {'>=$(=num(MonthStart(Max(Date1))))<$(=(Max(Date1)+1))'}, COMPLETED -= {'<$(=num(MonthStart(Max(Date1))))'} >

} distinct TICKETID)

Practically, put count(<F1>+<F2>+<F3> distinct TICKETID).

I hope this is what you need and works.

Best regards,

Cosmina

[divya_anand]

Hi Popescu,

This worked for me with some minute obvious script changes.

Thank you very much!

Regards,

Divya