Count Distinct with Set Analysis

[#1]

Hi,

I need some help with an expression of Count Distinct with Set Analysis, anyone can help?

The goal is calculate total number of jobs based on selected period.

For example, if user selects Dec 14, then count total number of jobs for Dec 13

if user selects Oct, Nov and Dec 14, then count total number of jobs for Oct, Nov and Dec 13

Here is my script below, non of them is working. please verify me if I am wrong. Thanks

Solution 1:

=count({$<Date=, Year=, Quarter=, Week=,

  Period = {">=$(=monthname(AddYears(min(MC_Period),-1))) <=$(=monthname(AddYears(max(MC_Period),-1)))"}

  DISTINCT JobNum)

Solution 2:

=count({<Period=, Date=, Year=, Quarter=,Week=>}if(Period >= vPeriodLYFromMin and Period <= vPeriodLYTo, JobNum,0))

vPeriodLYFromMin = monthname(AddYears(min(MC_Period),-1))

vPeriodLYTo = =monthname(AddYears(max(MC_Period),-1))

[#2]

Are you able to explain where did I do wrong with the first solution?

[ramoncova06]

I would need to see the data before I could tell you, though just from looking at it you are comparing the monthname what is the format ? MMYYYY, or MMMYYYY?

[#4]

Hi Ramon,

The format for monthname is MMMYYYY

[prieper]

Think that you need something like the attached

=COUNT({1 <MyDate_rM = {">=$(=MIN(MyDate_rM) -12) <=$(=MAX(MyDate_rM) -12)"} >} DISTINCT Expression1)

HTH Peter

[#6]

On the follow up with the same set analysis for the count, I have been struggling with a simple with Date and count with emplid:

=COUNT({<Years=, Month=, Quarter=, Week=, EFFDT =,Date={">=$(=Num ( MonthStart (Max(Date))))<=$(=Max(Date))"}>} DISTINCT(EMPLID))

Date is : Floor(trim(DATE(EFFDT,'MM/DD/YYYY'))) as Date, 

any help is appreciated...I get the numbers but totally wrong...am I missing something?