# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor III

## Dates in set analysis

HI all,

I have an issue with a measure that I am trying to set up.

The data has three different date fields handled by the autoCalendar:

EndDate: when a contract with an external company expires

OrderDate: when an order is placed

InvoiceDate: when the invoice is paid

So I am trying to find out if an order has been placed after the contract expiration date (may be one or hundreds of orders) and if so, what is the amount paid for the period after the contract has expired. So I am going like:

IF(MAX([OrderDate.autoCalendar.Date]) > [EndDate.autoCalendar.Date],

SUM({<[InvoiceDate.autoCalendar.Date]={">=[EndDate.autoCalendar.Date]"}>}InvoicedAmount))

This returns zeros when placed in a straight table with contract as a dimension. The first line of the expression works fine, the problem comes from the set analysis. How should I state that only the InvoicedAmount for the InvoiceDates after the EndDate should be summed?

Thank you!

1 Solution

Accepted Solutions
MVP

## Re: Dates in set analysis

Two ways you can do this

1) Create a flag in the script and use the flag in set analysis

If(OrderDate < EndDate, 1, 0) as Flag

and then

Sum({<Flag = {1}>} InvoicedAmount)

2) Use an if statement

Sum(If(OrderDate < EndDate, InvoicedAmount))

9 Replies
MVP

## Re: Dates in set analysis

Would you be able to share few rows of sample data and the output you expect to see out of it?

New Contributor III

## Re: Dates in set analysis

Hi Sunny! Thank you for responding!

Let's say we have this data:

 Contract EndDate OrderDate InvoiceDate InvoicedAmount 7777 31.01.2018 11.12.2017 15.12.2017 90 7777 31.01.2018 12.12.2017 02.02.2018 400 7777 31.01.2018 01.02.2018 05.02.2018 55 7777 31.01.2018 05.03.2018 22.03.2018 250

Then lines 1 and 2 should not be calculated as OrderDate < EndDate

In lines 3 and 4 OrderDate > EndDate, therefore the InvoicedAmount should be summed.

The outcome should be a straight table like this:

 Contract InvoicedAmountAfterEndDate 7777 305
MVP

## Re: Dates in set analysis

Two ways you can do this

1) Create a flag in the script and use the flag in set analysis

If(OrderDate < EndDate, 1, 0) as Flag

and then

Sum({<Flag = {1}>} InvoicedAmount)

2) Use an if statement

Sum(If(OrderDate < EndDate, InvoicedAmount))

Honored Contributor III

## Re: Dates in set analysis

Sum({<InvoicedAmount={"=OrderDate > EndDate"}>}InvoicedAmount)

MVP

## Re: Dates in set analysis

I would not suggest this approach sasiparupudi1‌... what if you have this data

 Contract EndDate OrderDate InvoiceDate InvoicedAmount 7777 31.01.2018 11.12.2017 15.12.2017 250 7777 31.01.2018 12.12.2017 02.02.2018 400 7777 31.01.2018 01.02.2018 05.02.2018 55 7777 31.01.2018 05.03.2018 22.03.2018 250
Honored Contributor III

## Re: Dates in set analysis

Yes, Sunny, it would be a problem .. Thanks for pointing that out

New Contributor III

## Re: Dates in set analysis

Sunny, you are a legend! Thank you, IF worked great!

I guess I overcomplicated this with my initial expression. But do you have any idea what would be the reason that my set analysis returns zeroes, it still looks like a legit expression to me.

MVP

## Re: Dates in set analysis

Set analysis is evaluated once per chart and what you actually needed was row by row evaluation of your condition... this can be only done using if statement

New Contributor III

## Re: Dates in set analysis

Alright, so this means that a set analysis expression should be used for the entire set of data and since my data is expanded into rows it wouldn't work.

Never thought of that. Thanks again!