If condition and Date range

[andymanu]

Hi All,

I got a small question but not sure how to resolve it.

Assume my objective is to select the Actual Cost for a selected time period and for a selected country.

I wrote the below expression but it does not give me the desired output,

Sum(If({<Date={'>=$(vYTDStartDate)<=$(vYTDEndDate)'}>} AND Country= 'Australia', Actual_Cost))

vYTDStartDate is 1/07/2018 and vYTDEndDate is 30/09/2018

I think the issue is with the way I specify the date range.

Could someone help me in this regards?

Thanks in advance.

Kind regards,

Andy

[andymanu · Accepted solution]

Hi,

Assume i got a working answer the above posted question.

instead using an IF statement, I applied a condition to the dimension (Country = Australia) and just wrote the expression including only the date range as below,

=Sum({<Date={'>=$(vYTDStartDate)<=$(vYTDEndDate)'}>} Actual_Cost)

Which seems working. However, I got an unexpected figure for the Actual_Cost for the month June 2019 (which is not yet reached).

I'll have a look and update the discussion accordingly. Appreciate your feedback too.

Thank you.

Regards,

Andy

[arvind1494]

=Sum({<Date={'>=$(vYTDStartDate)<=$(vYTDEndDate)'},Country= {'Australia'}>} Actual_Cost)

[tresesco]

Your set analysis syntax needs to be corrected. Try like:

Sum({<Date={">=$(vYTDStartDate)<=$(vYTDEndDate)"}, Country= {'Australia'}>}, Actual_Cost)

If this doesn't work, try to share a sample app to look at.

[andymanu]

Hi,

Sorry, I have done a mistake and after unticking the "Include Null Values" box under the Dimensions tab, I was able to remove the unwanted figures.

Thanks,

Andy