Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
harleen_singh
Creator III
Creator III

Set analysis sum between two given dates

Hello,

             i want to calculate sum between two given dates using set analysis. I am want to use greater than or equal to startdate and less than or equal to Enddate.

I am using this formula. but its not working even for 1 condition. How to use less than or equal to condition in ths formula??/

sum({$<OrderDate>={StartDate}>} Netamountpaid )

thanks

Lavi

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Hi Lavi,

venkatreddy's solution will work, but only if StartDate and EndDate are limited to one value in current selection.

For example, if a user selects none or more than one Date, the expression will not work.

You could combine the expression with a dollar sign expansion to handle date ranges:

= sum({<OrderDate= {">=$(=min(StartDate))<=$(=max(EndDate))"}> } Netamountpaid)

I assume you are not using StartDate and EndDate as dimensions in a table, with above expression, else you are running into troubles, since the set analysis will not care about the dimensions.

Regards,

Stefan

View solution in original post

13 Replies
sbaldwin
Partner - Creator III
Partner - Creator III

Hi, you need to add this into the set expression writing something like this:

sum({$<OrderDate={'<=$(=only(StartDate))'}>} Netamountpaid )

Thanks

Steve

Not applicable

Using following expression

sum({$<OrderDate={">=StartDate<=Enddate"}>Netamountpaid)

harleen_singh
Creator III
Creator III
Author

Steve,

             thanks for the response. in your formula what is StartDate??

is it variable or field name.

if it is variable then where to define it?

in my formula StartDate is field name

Regards

Harleen

harleen_singh
Creator III
Creator III
Author

venkatreddy,

thanks for the response. in your formula what is StartDate??

is it variable or field name.

if it is variable then where to define it?

in my formula StartDate is field name

Regards

Harleen

Not applicable

field Name

sbaldwin
Partner - Creator III
Partner - Creator III

Hi, yes i was also assuming it was a field name

Thanks

Steve

swuehl
MVP
MVP

Hi Lavi,

venkatreddy's solution will work, but only if StartDate and EndDate are limited to one value in current selection.

For example, if a user selects none or more than one Date, the expression will not work.

You could combine the expression with a dollar sign expansion to handle date ranges:

= sum({<OrderDate= {">=$(=min(StartDate))<=$(=max(EndDate))"}> } Netamountpaid)

I assume you are not using StartDate and EndDate as dimensions in a table, with above expression, else you are running into troubles, since the set analysis will not care about the dimensions.

Regards,

Stefan

Anonymous
Not applicable

Hi Stefan,

Will the same expression works for current year and last year data as well, i tried below expression

For current year

Sum({<Year = {$(=max(Year))},Date={">=$(=min(Promo_Date_From))<=$(=max(Promo_Date_To))"}>} [Net Sales])

here am usning promo event as dimension.

it's giving 0 as output. Please suggest.

Regards,

Sadasiva

Anonymous
Not applicable

Hi Stefan ,

i m using a set analysis expression . in my report i m showing data for LMTD.  In Dimension i have LinkCommonDate and used:

=if(LinkCommonDate>=$(v1MonthMinDate),Date(LinkCommonDate))

where v1MonthMinDate is floor(Date(MonthStart( AddMonths( max(SurveyDate)   ,-1)),'YYYY-MM-DD'))

but when i m selecting a particular dealer it does not consider LMTD rule   and gives data for other months also.

How should i restrict my Date Dimension in report to show data for LMTD only .