Set analysis

[bhavvibudagam]

Hi Experts,

Can any one please help me on below requirement.

I have a Due date field in DD/MM/YYYY format. From that field based on Weekday need to derive the below Measure.

Count(Distinct issuenum) where duedate>= start of week(Monday) and <=end of week(sunday)

how to add the where condition on set analysis. Please help me on this.

Thanks in advance.

[shiveshsingh]

=Count(DISTINCT{<Date(DueDate,'MM/DD/YYYY') ={">=$(=Date(WeekStart(DueDate,0,0),'MM/DD/YYYY'))<=$(=Date(Weekend(DueDate,0,0),'MM/DD/YYYY'))"}>}issuenum)

Please share o/p for =Date(WeekStart(DueDate,0,0),'MM/DD/YYYY') & =Date(Weekend(DueDate,0,0),'MM/DD/YYYY')

[sasiparupudi1 · Accepted solution]

I think you need a max here

Count(DISTINCT{<DueDate ={">=$(=Date(WeekStart(Max(DueDate),0,0),'MM/DD/YYYY'))<=$(=Date(Weekend(Max(DueDate),0,0),'MM/DD/YYYY'))"}>}issuenum)

[PrashantSangle]

Hi shiveshsingh‌,

In set analysis, you can not format left hand side field of = symbol

=Count(DISTINCT{<Date(DueDate,'MM/DD/YYYY') ={">=$(=Date(WeekStart(DueDate,0,0),'MM/DD/YYYY'))<=$(=Date(Weekend(DueDate,0,0),'MM/DD/YYYY'))"}>}issuenum)

Regards,

[shiveshsingh]

Thanks Prashant for correction.

[Chanty4u]

is that table box use aggr to work it out

=aggr(only(DISTINCT{<Date(DueDate,'MM/DD/YYYY') ={">=$(=Date(WeekStart(DueDate,0,0),'MM/DD/YYYY'))<=$(=Date(Weekend(DueDate,0,0),'MM/DD/YYYY'))"}>}issuenum),issuenum)

[Anonymous]

Hi,

Check with below condition. In your case you need to use the fields that your using inside the table in set analysis.

Count(DISTINCT{<Field1,Field2,DueDate ={">=$(=Date(WeekStart(Max(DueDate),0,0),'MM/DD/YYYY'))<=$(=Date(Weekend(Max(DueDate),0,0),'MM/DD/YYYY'))"}>}issuenum)

Thanks,

Venkata Sreekanth