topic Re: Fitting a dollar expression from a date into set analysis in QlikView

Fitting a dollar expression from a date into set analysis

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

Hi,

I have written a dollar expansion that returns the minium date from the 'master month date' list box, then subtracts 12 months.

='$(=DATE(ADDMONTHS(MIN(DATE([Master Month Date],'DD/MM/YYYY')),-12,1),'DD/MM/YYYY'))'

The above formula is in a text box and works

The next step is to fit this into set analysis:

Count(DISTINCT {1<[ClosedMonth]={">=$(=DATE(ADDMONTHS(MIN(DATE([Master Month Date],'DD/MM/YYYY')),-12,1),'DD/MM/YYYY'))"}>} ReferenceNumber)

What I would like to return is the data set from 12 months before the current minimum date selection.

Closed month is the dimension and the above count formula is the expression.

Is this possible?

Thanks,

Neil.

Re: Fitting a dollar expression from a date into set analysis

MK_QSL — Wed, 12 Jul 2017 13:13:53 GMT

What is the Format of Closed Month?

Re: Fitting a dollar expression from a date into set analysis

sunny_talwar — Wed, 12 Jul 2017 13:20:56 GMT

Also, how are master month date and Closed Month related to each other? May be ignore selection in master month date

Count(DISTINCT {1<[ClosedMonth]={">=$(=Date(AddMonths(Min([Master Month Date]), -12, 1),'DD/MM/YYYY'))"}, [master month date] = >} ReferenceNumber)

Re: Fitting a dollar expression from a date into set analysis

tinkerz1 — Wed, 12 Jul 2017 13:40:27 GMT

Master month date is in my master calendar, it stores all month end dates for all transactions,

Therefore when selected this will link to closed month in the fact table to the Master calendar.

I have done this many times, but not tried to have the Master Month Date 12 months before.

an example is

Count(distinct {<[ResolvedMonth]=$::[Master Month Date],[Date Reference Info]={'Resolved Date'},OverallOutcome={'Upheld','Partially Upheld'}>} ReferenceNumber)