Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
steve_br
Creator
Creator

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
sunny_talwar

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

View solution in original post

9 Replies
sunny_talwar

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

steve_br
Creator
Creator
Author

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
sunny_talwar

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
Master III
Master III

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

sunny_talwar

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
Master III
Master III

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

steve_br
Creator
Creator
Author

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.


sunny_talwar

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

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!