Set Analysis - Date Comparison

[kangaroomac]

Good Day,

Not sure what I'm missing, but for some reason in Set Analysis when I try to do a calculation pending on a date comparison I'm not getting the desired output.

When I use an IF statement, the desired output is returned.

Can anyone see what I'm missing?

Attached is a sample.

The IF Statement:

Count(Distinct

  IF(

     [First Date] > MonthEnd([Second Date])

      , [Field]))

Set Analysis:

Count( Distinct

{<

        [First Date]={">$(=MonthEnd([Second Date]))"}

>}

        [Field])

[tresesco]

Are you using this in a chart? If so, you might get different result with IF and SET because, SET is not chart row sensitive, i.e. - gets evaluated once for a chart.

[sunny_talwar · Accepted solution]

The problem is, set analysis is evaluated once per chart, whereas if statement can be evaluated for each row. You data is like this

So when you use MonthEnd([Second Date]), if statement understands it as Oct 31st 2016 for first row and Sep 30th 2016 for the second row. But within your set analysis, the MonthEnd([Second Date]) is null because there are two different months and since we have not provided any sort of aggregation, it doesn't know what to show.

There is a way to use search string within set analysis, but you will need a field that uniquely defines each of the row. For the above data set, you might be able to use Dimension since it is making the two rows unique, but I doubt you would be able to use that for your real data because Month Year might repeat for different Field. For your real data, you might be able to create a new field using Dimension and Field

LOAD AutoNumber(Dimension&Field) as NewField,

          Dimension,

          Field,

          [First Date],

          [Second Date]

FROM Source;

and then try something like this:

Count( Distinct

  {<

        NewField = {"=[First Date] > Max([Second Date])"}

  >}

    NewField)

I hope this make sense?

Best,

Sunny

[kangaroomac]

Hi Sunny,

That does solve the sample I've given (and is most likely the root cause of my actual issue), thank you.

I have tried to implement it on the actual data with no avail.

I am using it in an As-Of date (being the dimension) that is not part of the Fact Table.

Is there any way to use the Aggr function in Set Analysis with your above solution or do you have another suggestion?

[sunny_talwar]

There has been few occasions where the Aggr() have worked, but most of the time, Aggr() doesn't really work within set analysis (or at least should be avoided). From what it sounds you might be better of using if statement rather than using set analysis, but that is just a general idea without having too much details about your data model. Would you be able to provide a sample of what you are doing to be able to suggest you if set analysis is possible or not?

Best,

Sunny

[kangaroomac]

Hi Sunny,

I have marked this answer as correct as it was the correct answer for the sample given (sure it will help someone else in the future too).

As for the real issue, I have changed the data model and amened the load script to accommodate and was successful.

Thanks for pointing me in the right direction.