topic how to get max date between 2 dates variables in QlikView

how to get max date between 2 dates variables

Shinigaloo — Thu, 10 Dec 2020 13:00:23 GMT

Hello all,

i have a measure a bar chart in which i measure number of equipment per category and user and last occurrence of model

bar chart worked and the measure very well , here's the measure

measure = Sum(Aggr(If([Date] = Max(TOTAL <[Category], User> Date), Count(Model)), [Date], Category, User))

Now my [Date ] vary depending on the selected period , it should be [Date]=< vdatefinformat and

[Date] >=vdatedebformat

how can i merge this condition into my measure , so that i get the right result

Please can anyone help me ?

Re: how to get max date between 2 dates variables

edwin — Thu, 10 Dec 2020 16:23:15 GMT

assuming the dates are in the correct format just add to your set analysis:

Date={">=$(vdatedebformat)<=$(vdatefinformat )"}

i would say in all your calculations :
Sum(Date={">=$(vdatedebformat)<=$(vdatefinformat )"}Aggr(If([Date] = Max(TOTAL <[Category], User> Date={">=$(vdatedebformat)<=$(vdatefinformat )"}Date), Count( Date={">=$(vdatedebformat)<=$(vdatefinformat )"} Model)), [Date], Category, User))

obviously you need to test each of the aggregation.

see attached Set Analysis reference i found here but cant find teh actual thread anymore

Re: how to get max date between 2 dates variables

Stewarttt — Mon, 14 Dec 2020 09:19:39 GMT

Supposing you have a table as below screenshot shown, and you need to find array formulas can help you to find max or min value between two dates in Excel. Finding max value between date 2016/7/1 and 2016/12/1: 2. Enter the below and B5:B17 is the value range contains the max and min values you will find.

Re: how to get max date between 2 dates variables

Shinigaloo — Mon, 14 Dec 2020 15:38:09 GMT

Thanks you edwin , it' worked

Re: how to get max date between 2 dates variables

richardchin — Sat, 02 Apr 2022 12:20:29 GMT

Instead of giving a date such as '1/1/1973' we can give blank string '' which should get cast to the earliest date as per date type used.. e.g. CAST('' as date) Mypascoconnect retruns "1900-01-01", CAST('' as datetime) returns "1900-01-01 00:00:00.000" and CAST('' as datetime2) returns "1900-01-01 00:00:00.0000000". This works on SQL Server 2019.