DSE and dates in reference lines

[tinkerz1]

Hi,

I am tring to get the below formula to work and I am having trouble with the DSE part.

[Reporting Date]={$(=date(vMaxTtmp))}

The full formula:

sum(distinct {<[Issue Owned by]={'Market Risk All'},[Threshold Assessment Of The Risk]={'Medium'},[Threshold On Target / Overdue]={'On Target'},[Threshold]={'Red Threshold'},[Reporting Date]={$(=date(vMaxTtmp))}>}[Threshold Data])

I load in all the Reporting data like this:

date([Reporting Date],'DD/MM/YYYY') 

And I find the VMaxTtmp like this:

TMP:
LOAD
min([Threshold Reporting Date]) as mini,
max([Threshold Reporting Date]) as maxi
Resident [Threshold_table];
Let vMinTtmp = peek('mini',0,'TMP');
Let vMaxTtmp = peek('maxi',0,'TMP');
drop table TMP;

When swap the DSE in the first formula to:Reporting Date]={'01/01/2016'} I return the correct answer but when I use the first formula I do not get a value, when test in a text box this formula: date(vMaxTtmp) . The right answer is returned ( 01/01/2016). So where is my DSE going wrong?

Thanks

[sunny_talwar]

Just to be sure 01/01/2016 is DD/MM/YYYY and not MM/DD/YYYY, I will add the date format within my date function and see if that helps:

Sum(Distinct {<[Issue Owned by]={'Market Risk All'}, [Threshold Assessment Of The Risk]={'Medium'}, [Threshold On Target / Overdue]={'On Target'}, [Threshold]={'Red Threshold'}, [Reporting Date]={$(=Date(vMaxTtmp, 'DD/MM/YYYY'))}>}[Threshold Data])

[Anonymous]

may be this?

sum(distinct {<[Issue Owned by]={'Market Risk All'},[Threshold Assessment Of The Risk]={'Medium'},[Threshold On Target / Overdue]={'On Target'},[Threshold]={'Red Threshold'},[Reporting Date]={'=$(=date(vMaxTtmp,'DD/MM/YYYY'))}>} [Threshold Data])

[tinkerz1 · Accepted solution]

This was my solution "$(=date(vMaxTtmp))"}

I think that by not including the "" the user would have to select a range of dates, and as 01/01/2016 is not selected nothing is returned.