Qlik Community

Qlik Sense App Development

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

acbishop2
Contributor

Calculation within Set Analysis

Hello All,

First Problem:

I need to add all values in my [Total Leads] field that fall within the month of the last date given. The max([Date]) will change based on restrictions applied in the load script. I started with If() statements, but they are not as flexible and do not allow a max() aggregation.

What I need, then, is something similar to one of these functions:

Sum({$<Month([Date])={"$(=Month(Max([Date])))"}>}[Total Leads])

Sum({$<[Date]={"$(>=MonthStart(Max([Date])))"}>*$<Date={"$(<=MonthEnd(Max([Date])))"}>}[Total Leads])

Second Problem:

I need to restrict the dimension of a graph in a similar manner. The dimension would be [Date.autoCalendar.Date], but I only want it to show dates for the month of the last date available (max([Date])). Again, I started with If(), but this does not allow the max(). Any work-arounds?

As always, thank you for the help.

Aaron

1 Solution

Accepted Solutions
Digvijay_Singh
Honored Contributor III

Re: Calculation within Set Analysis

Try this may be -

Sum({$<[Date]={">=$(=MonthStart(Max([Date])))<=$(=MonthEnd(Max([Date])))"}>}[Total Leads])

4 Replies
Digvijay_Singh
Honored Contributor III

Re: Calculation within Set Analysis

Try this may be -

Sum({$<[Date]={">=$(=MonthStart(Max([Date])))<=$(=MonthEnd(Max([Date])))"}>}[Total Leads])

acbishop2
Contributor

Re: Calculation within Set Analysis

Thanks! That solves the first problem. Any ideas on the second?

Digvijay_Singh
Honored Contributor III

Re: Calculation within Set Analysis

I think by deselecting zero values from Add-on check box or removing nulls from dimension properties it should only show values for filtered dates in measure. Can you check this out.

acbishop2
Contributor

Re: Calculation within Set Analysis

Wow, that was a lot easier than I was making it! Thanks a bunch!

Community Browser