Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Yorick_Stevens
Partner
Partner

Identifiers to select on all but one specific dimension

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

1 Solution

Accepted Solutions
Yorick_Stevens
Partner
Partner
Author

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.

View solution in original post

2 Replies
Anil_Babu_Samineni

Few comments like

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

or

https://community.qlik.com/t5/QlikView-Documents/Ignore-all-selections-except-some-specific-fields-u...

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Yorick_Stevens
Partner
Partner
Author

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.

View solution in original post