topic DSE and dates in reference lines in QlikView

DSE and dates in reference lines

tinkerz1 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: DSE and dates in reference lines

sunny_talwar — Thu, 03 Mar 2016 11:29:40 GMT

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])

Re: DSE and dates in reference lines

Anonymous — Thu, 03 Mar 2016 11:51:59 GMT

may be this?

Re: DSE and dates in reference lines

tinkerz1 — Thu, 03 Mar 2016 12:07:20 GMT

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.