Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Eliminate Current Month

Hi Team,

I have req like exclude the current month while calculate the average ref lines

Eg:

If i selected a year 2018 (Jul 2017 to Feb 18 ) will come .

Total counts = 112 (Include feb 18). But I want to  exclude feb 18 while adding ref lines

At the same time it won't affect rest of the previous years

My expression  :Count({$<[Group] = {'dfgdg'}>} DISTINCT xyz ))  it will return 112

Here how can i exclude feb ?

1.PNG

REF line expression is :  

Count({$<[Group] = {'dfgdg'}>} DISTINCT xyz ))  / Count ([Year Month])

if i remove the feb in the calculation avg line should b 15.9

Please help on this

Thanks

5 Replies
Highlighted
Specialist II
Specialist II

Hi

Try this

Count({$<[Group] = {'dfgdg'}>} DISTINCT xyz ))  /  Count({$<[Year Month]={"<$(=MonthYear(today()))"}>}[Year Month])



Highlighted
Master III
Master III

You need to exclude current month thru set analysis, need to correct formatting and date fieldname -


Count({$<[Group] = {'dfgdg'},Date = {"<$(=MonthStart(today()))"}>} DISTINCT xyz ))

Highlighted
Specialist
Specialist

Use this in set modifier to eliminate current month count.

May be This

count({<Month -= {"$(=Date(MonthStart(Today()), 'MMM-YYYY'))"}>}DISTINCT xyz )

Highlighted
Contributor III
Contributor III

I think that you need to exclude de Month-Year, because that's the format of the dimension from the bottom of the barchart.


If you have a Master Calendar which contains the MonthYear field, just use the following expression:


=Count({$<[Group] = {'dfgdg'}, MonthYear-={"$(=Date(MonthStart(Today()), 'MMM-YYYY'))"}>} DISTINCT xyz )

Highlighted
Contributor III
Contributor III

I need to exclude the count also  in the numerator


in the denominator  i can do.


How can do it ?