Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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))
Would you be able to share few rows of sample data and the output you expect to see out of it?
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 |
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))
Sum({<InvoicedAmount={"=OrderDate > EndDate"}>}InvoicedAmount)
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 |
Yes, Sunny, it would be a problem .. Thanks for pointing that out
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.
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
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!