Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
Showing results for 
Search instead for 
Did you mean: 
Creator II
Creator II

Fitting a dollar expression from a date into set analysis


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?



3 Replies

What is the Format of Closed Month?


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)

Creator II
Creator II

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)