Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

steve_br
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

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

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?

steve_br
New Contributor III

Re: Dates in set analysis

Hi Sunny! Thank you for responding!

Let's say we have this data:

    

ContractEndDateOrderDateInvoiceDateInvoicedAmount
777731.01.201811.12.201715.12.201790
777731.01.201812.12.201702.02.2018400
777731.01.201801.02.201805.02.201855
777731.01.201805.03.201822.03.2018250

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:

  

ContractInvoicedAmountAfterEndDate
7777305

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

sasiparupudi1
Honored Contributor III

Re: Dates in set analysis

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

Re: Dates in set analysis

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

ContractEndDateOrderDateInvoiceDateInvoicedAmount
777731.01.201811.12.201715.12.2017250
777731.01.201812.12.201702.02.2018400
777731.01.201801.02.201805.02.201855
777731.01.201805.03.201822.03.2018250
sasiparupudi1
Honored Contributor III

Re: Dates in set analysis

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

steve_br
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.


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

steve_br
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!

Community Browser