Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
mario2
Contributor
Contributor

Ignore only date selection but comparing to variables

Hi everyone,

I am facing the following problem:

I have a dashboard where I can check the count of user_id in a specific period of time (selected by filters) and the corresponding previous period (using variables).

Current period: Count(user_id_status)

Previous period: count({1<[MasterDate.autoCalendar.NumDate]={">=$(=$(vPreviousStartDate))<=$(=$(vPreviousEndDate))"}>}user_id_status)

vPreviousEndDate: =num(date(min({$}[MasterDate.autoCalendar.Date])-1,'dd/MM/yyyy'))

vPreviousStartDate: =num(date(min({$}[MasterDate.autoCalendar.Date])-$(vDays),'dd/MM/yyyy'))

vDays: =max({$}[MasterDate.autoCalendar.Date])-min({$}[MasterDate.autoCalendar.Date])

Here is everything working as expected. But now I found out that I have a problem with other filters because for Previous period, we have the "1". Let' say I want to add filters on device_type, then I know I could do that

Previous period: count({1<[MasterDate.autoCalendar.NumDate]={">=$(=$(vPreviousStartDate))<=$(=$(vPreviousEndDate))"},[device_type]=P([device_type])>}user_id_status)

But if I include more filters, then I would need always to add this lines to every measure which is not really scalable for future developments.

My question is.. can I put a general formula where it picks all the filters except the Dates?

Note: NumDate is just the num(DateField) to be able to comparte them easier

2 Replies
treysmithdev
Partner Ambassador
Partner Ambassador

You would either need to remove data selections from your $ set or include desired selections in your 1 set.

 

I suggest making a variable that excludes all of you date fields and using that in your set.

vSADateExclude = 'Date=,Week=,Month=,[Month-Year]=,Quarter=,Year='

Then do:

vPreviousEndDate: =num(date(min({$<$(vSADateExclude)>}[MasterDate.autoCalendar.Date])-1,'dd/MM/yyyy'))

vPreviousStartDate: =num(date(min({$<$(vSADateExclude)>}[MasterDate.autoCalendar.Date])-$(vDays),'dd/MM/yyyy'))

vDays: =max({$<$(vSADateExclude)>}[MasterDate.autoCalendar.Date])-min({$}[MasterDate.autoCalendar.Date])

 

This will keep it in one place, allowing you to add/remove fields as necessary without going into all of your expressions.

Blog: WhereClause   Twitter: @treysmithdev
mario2
Contributor
Contributor
Author

Thanks! How would you then calculate the count of the previous period?