topic Re: Identifiers to select on all but one specific dimension in App Development

Identifiers to select on all but one specific dimension

Yorick_Stevens — Sat, 16 Nov 2024 20:10:35 GMT

Hi,

I am stuck in a formula to calculate the YTM data for my sales, i created four variables stating the respective dates (beginning and ending of my selection) and (the beginning and ending of my selection minus 1 year). The Measure currently looks like:

Count( {1<[ReferenceDate.Calendar.DateNum] = {">=$(=$(SelectedDateMinYTMmin1)) <$(=$(SelectedDateMaxYTMmin1))"}>} Distinct( ReservationNumber))

I have two issues:

1. The measure doesn't take any other filter into account (obviously because of the identifier 1) So I am looking for the statement so that my measure looks at all the "ReferenceDate.Calendar" fields but still takes any other filter into account (without me specifying all the different fields like Name=$::Name (with "Name" a dimension")

2. If i omit a month in my selection, the measurement doesn't do the same. Could I change my ">=" & "<" to the variables to a set of data instead
the variable looks like:
NUM(IF( GETSELECTEDCOUNT([ReferenceDate.Calendar.Year])=0 AND
GETSELECTEDCOUNT([ReferenceDate.Calendar.Month]) = 0 AND
GETSELECTEDCOUNT([ReferenceDate.Calendar.Date])=0 AND
GETSELECTEDCOUNT([ReferenceDate.Calendar.DateNum])=0 AND
GETSELECTEDCOUNT([ReferenceDate.Calendar.FiscalYear])=0 AND
GETSELECTEDCOUNT([ReferenceDate.Calendar.MonthFY])=0,
$(vT_DateMaxYTMmin1), MONTHEND(MAX(Addmonths([ReferenceDate.Calendar.Date],-12)))))

Thanks for helping me out,
Yorick

Re: Identifiers to select on all but one specific dimension

Anil_Babu_Samineni — Sat, 31 Aug 2019 04:10:57 GMT

Few comments like

1) Count( {1<[ReferenceDate.Calendar.DateNum] = {">=$(=$(SelectedDateMinYTMmin1)) <$(=$(SelectedDateMaxYTMmin1))"}, %Key=$::[ReferenceDate.Calendar*]>} Distinct( ReservationNumber))

https://community.qlik.com/t5/QlikView-Documents/Ignore-all-selections-except-some-specific-fields-using-Set/ta-p/1482031

2) Quite okay to me, What is your intention here?

Re: Identifiers to select on all but one specific dimension

Yorick_Stevens — Tue, 12 Nov 2019 16:54:55 GMT

Hi All,

For persons who are stuck with the same issue.
The solution is:

Count( {$< [ReferenceDateNum] = P( {1<[ReferenceDateNum]={">=$(=$(SelectedDateMinYTMmin1)) <$(=$(SelectedDateMaxYTMmin1))"} >} [ReferenceDateNum]),
[ReferenceMonth] = P( {1<[ReferenceDateNum]={">=$(=$(SelectedDateMinYTMmin1)) <$(=$(SelectedDateMaxYTMmin1))"} >} [ReferenceMonth])>}

Total< [ReferenceDateNum],
[ReferenceMonth]> Distinct ( ReservationNumber))

My Mesure takes a look at all filters except the for 'ReferenceDateNum' and 'ReferenceMonth' still takes into account the specific filter on ReferenceDateNum.

Hoping this helps anyone.