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: 
A_PR
Contributor III
Contributor III

Max Month in Set Analysis

count(distinct [id])/
(RangeSum(Below(count(distinct [id]),0,(NoOfRows()-RowNo()+1)))+
RangeSum (above(count(distinct [id]),1,(RowNo()-1))))

I have the formula for a graph which is working perfectly fine. I need to add a criteria where this graph only show data of the last month of reference_date.

created a variable: vLastMonth =Month(Max(reference_date))

Count(DISTINCT {<month(reference_date) = {"$(vLastMonth)"}>} [id]) /
(RangeSum(Below(Count(DISTINCT {<month(reference_date) = {"$(vLastMonth)"}>} [id]),0,NoOfRows() - RowNo() + 1)) +
RangeSum(Above(Count(DISTINCT {<month(reference_date) = {"$(vLastMonth)"}>} [id]),1,RowNo() - 1)))

This formula gives error in expression.

 

Can someone please let me know what am I doing wrong  in this formula?

1 Reply
MatheusC
Specialist II
Specialist II

@A_PR 
Previously create a month field in the script

Month(reference_date) -> Month
MonthName(reference_date) -> MonthYear

Or view through a date range

[reference_date]={">=$(=monthstart(max(reference_date)))<=$(=monthend(max(reference_date)))"}

Formatting Date may be necessary depending on the format of your data.

- Matheus

Did you find a solution to your question? Mark the solution as accepted and if you found it useful, press the like button!