-
Re: Dates in set analysis
Sunny Talwar Apr 6, 2018 7:39 AM (in response to Steve Br.)Would you be able to share few rows of sample data and the output you expect to see out of it?
-
Re: Dates in set analysis
Steve Br. Apr 6, 2018 8:08 AM (in response to Sunny Talwar )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 -
Re: Dates in set analysis
Sunny Talwar Apr 6, 2018 8:20 AM (in response to Steve Br.)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))
-
Re: Dates in set analysis
Steve Br. Apr 9, 2018 9:33 AM (in response to Sunny Talwar )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
Sunny Talwar Apr 9, 2018 12:24 PM (in response to Steve Br.)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
-
Re: Dates in set analysis
Steve Br. Apr 10, 2018 3:35 AM (in response to Sunny Talwar )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!
-
-
-
-
Re: Dates in set analysis
Sasidhar Parupudi Apr 6, 2018 9:44 AM (in response to Steve Br.)Sum({<InvoicedAmount={"=OrderDate > EndDate"}>}InvoicedAmount)
-
297376.qvw 152.8 K
-
Re: Dates in set analysis
Sunny Talwar Apr 6, 2018 9:46 AM (in response to Sasidhar Parupudi)I would not suggest this approach Sasidhar... 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 -
Re: Dates in set analysis
Sasidhar Parupudi Apr 6, 2018 9:52 AM (in response to Sunny Talwar )Yes, Sunny, it would be a problem .. Thanks for pointing that out
-
-
-
-