Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I want to create a list of customer which have revenue in a certain period, but at the same time have no revenue in another period.
The periods are variable via 2 slider-objects.
I have very simple sample data:
fact:
LOAD * INLINE [
PostingDate, CustomerNo, Revenue
01/01/2015, KL1, 1000
01/01/2016, KL1, 1500
01/04/2015, KL2, 800
01/11/2015, KL2, 500
01/03/2015, KL2, 300
];
I have created 2 sliderobjects with min and max value:
Slider object 1 fills 2 variables: vMinDateValueRevenue / vMaxDateValueRevenue
Slider object 2 fills 2 variables: vMinDateValueNoRevenue / vMaxDateValueNoRevenue
I have created a straight table with dimension "Customer" and expression "Revenue".
Now I want to show as result:
Sum of Revenue within the selected period between vMinDateValueRevenue and vMaxDateValueRevenue, but only if there is NO revenue in the period between vMinDateValueNoRevenue en vMaxDateValueNoRevenue.
I assume this can be done via Set Analysis, but I cant find the correct syntax.
Can anybody assist?
Maybe something like
=Sum({<
PostingDate = {">=$(=Date(vMinDateValueRevenue))<=$(=Date(vMaxDateValueRevenue))"},
CustomerNo = {"=Sum({<Posting Date = {">=$(=Date(vMinDateValueNoRevenue))<=$(=Date(vMaxDateValueNoRevenue))"}>}Revenue)=0"}
>} Revenue)
Double check that the date variables are correctly expanded in the set modifier:
Same could maybe also be achieved using p() and e() function:
=Sum({<
CustomerNo =
p({<PostingDate = {">=$(=Date(vMinDateValueRevenue))<=$(=Date(vMaxDateValueRevenue))"} >} )
*
e({<Posting Date = {">=$(=Date(vMinDateValueNoRevenue))<=$(=Date(vMaxDateValueNoRevenue))"} >} )
>} Revenue)
For your sample what would be good values for setting the variables and the expected output?
Maybe something like
=Sum({<
PostingDate = {">=$(=Date(vMinDateValueRevenue))<=$(=Date(vMaxDateValueRevenue))"},
CustomerNo = {"=Sum({<Posting Date = {">=$(=Date(vMinDateValueNoRevenue))<=$(=Date(vMaxDateValueNoRevenue))"}>}Revenue)=0"}
>} Revenue)
Double check that the date variables are correctly expanded in the set modifier:
Same could maybe also be achieved using p() and e() function:
=Sum({<
CustomerNo =
p({<PostingDate = {">=$(=Date(vMinDateValueRevenue))<=$(=Date(vMaxDateValueRevenue))"} >} )
*
e({<Posting Date = {">=$(=Date(vMinDateValueNoRevenue))<=$(=Date(vMaxDateValueNoRevenue))"} >} )
>} Revenue)
In the first solution, there seems to be a problem (the last part of the syntax of "CustomerNo" is underlined in red
CustomerNo = {"=Sum({<Posting Date = {">=$(=Date(vMinDateValueNoRevenue))<=$(=Date(vMaxDateValueNoRevenue))"}>}Revenue)=0"}
the second solution seems to be working perfectly.
Thanks!!
Ah, I think that's because nested advanced searches (advanced search enclosed in double quotes embedded into advanced search enclosed in double quotes). You need to replace one pair with single quotes:
CustomerNo = {"=Sum({<Posting Date = {'>=$(=Date(vMinDateValueNoRevenue))<=$(=Date(vMaxDateValueNoRevenue))'}>} Revenue)=0"}
After I posted my last answer I reviewed and then assumed that the first one should be logically correct, while the second one would filter the correct CustomerNo, but should not restrict the Revenue to the selected date range.
I think this should be correct for this requirement:
=Sum(
{<
PostingDate = {">=$(=Date(vMinDateValueRevenue))<=$(=Date(vMaxDateValueRevenue))"},
CustomerNo =
e({<Posting Date = {">=$(=Date(vMinDateValueNoRevenue))<=$(=Date(vMaxDateValueNoRevenue))"}>})
>} Revenue)