topic Re: Distinct Count between dates in QlikView

Distinct Count between dates

Dmichel0729 — Mon, 11 Jan 2021 19:35:25 GMT

Hello all,

I am trying to change an expression that previously looked at a date range up to three months prior. I now need to change it to show data prior to 90 days. Below is the expression I have developed, but the output I receive in my chart is "-". The font in red is what I have added to change the max date to 90 days. Previously, the red text was Date(MonthEnd(Max(MonthYear), -3).

Num(count(DISTINCT{1< [Site]={'Site Reviewed On Time', 'Site Reviewed Late'}, MonthYear={"$(='>=' & Date(MonthStart(Max(MonthYear), -13), 'MMM-YYYY') & '<=' & Date(=Date(today(), -90), 'MMM-YYYY')"}, [Record Status] -={'Postponed', 'Cancelled'}>} RPA)/count(DISTINCT{1< [Site]-={''}, MonthYear={"$(='>=' & Date(MonthStart(Max(MonthYear), -13), 'MMM-YYYY') & '<=' & Date(=Date(today(), -90)), 'DD-MM-YYYY')"}, [Record Status] -={'Postponed', 'Cancelled'}>} RPA),'#,##0%')

Any help would be appreciated!

Thank you

Dylan

Re: Distinct Count between dates

Anil_Babu_Samineni — Mon, 11 Jan 2021 19:53:45 GMT

Perhaps this?

Num(count(DISTINCT{1< [Site]={'Site Reviewed On Time', 'Site Reviewed Late'},MonthYear={">=$(=Date(MonthStart(AddMonths(Max(MonthYear), -13)), 'MMM-YYYY'))<=$(=Date(MonthStart(AddMonths(Max(MonthYear), -3)), 'MMM-YYYY'))"}, [Record Status] -={'Postponed', 'Cancelled'}>} RPA)/count(DISTINCT{1< [Site]-={''}, MonthYear={">=$(=Date(MonthStart(AddMonths(Max(MonthYear), -13)), 'MMM-YYYY'))<=$(=Date(MonthStart(AddMonths(Max(MonthYear), -3)), 'MMM-YYYY'))"}, [Record Status] -={'Postponed', 'Cancelled'}>} RPA),'#,##0%')

Re: Distinct Count between dates

Dmichel0729 — Mon, 11 Jan 2021 19:48:26 GMT

Hello Anil,

Where you stated '"<=$(=Date(MonthStart(AddYears(Max(MonthYear), -3))" I am actually trying to make it reflect 90 days and no longer 3 months. Sorry my explanation in the question may have been a little misleading. Do you know how I would change your above expression to reflect 90 days prior?

Re: Distinct Count between dates

Anil_Babu_Samineni — Mon, 11 Jan 2021 19:53:15 GMT

What is your date field and how it is? Today() is not the correct one, IF you calculate MonthYear from DateField - You can try? But actually you should be having similar functionality when you compare MonthYear with Date so, I would suggest to use DateField instead MonhtYear. If not, Please explain more with some dataset ..

<=$(Date(Max(DateField, -30)))