Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
kangaroomac
Partner
Partner

Set Analysis - Date Comparison

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])

1 Solution

Accepted Solutions
sunny_talwar

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

Capture.PNG

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

View solution in original post

5 Replies
tresesco
MVP
MVP

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

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

Capture.PNG

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

View solution in original post

kangaroomac
Partner
Partner
Author

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
Partner
Partner
Author

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.