Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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])
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])
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.