topic Set analysis sum between two given dates in QlikView

Set analysis sum between two given dates

harleen_singh — Thu, 18 Aug 2011 09:05:37 GMT

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

Re: Set analysis sum between two given dates

sbaldwin — Thu, 18 Aug 2011 09:09:19 GMT

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

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

Thanks

Steve

Set analysis sum between two given dates

Thu, 18 Aug 2011 09:11:52 GMT

Using following expression

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

Set analysis sum between two given dates

harleen_singh — Thu, 18 Aug 2011 09:23:42 GMT

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

Set analysis sum between two given dates

harleen_singh — Thu, 18 Aug 2011 09:54:37 GMT

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

Set analysis sum between two given dates

Thu, 18 Aug 2011 10:04:57 GMT

field Name

Set analysis sum between two given dates

sbaldwin — Thu, 18 Aug 2011 10:32:34 GMT

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

Thanks

Steve

Set analysis sum between two given dates

swuehl — Thu, 18 Aug 2011 10:38:57 GMT

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

Re: Set analysis sum between two given dates

Anonymous — Sun, 02 Jul 2017 09:04:29 GMT

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

Re: Set analysis sum between two given dates

Anonymous — Sun, 02 Jul 2017 11:51:11 GMT

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 .

Re: Set analysis sum between two given dates

Anonymous — Sun, 02 Jul 2017 11:53:37 GMT

@Stefan Wühl

please help

Re: Set analysis sum between two given dates

passionate — Sun, 02 Jul 2017 13:54:50 GMT

Hi,

Usually in this kind of scenario for start and end date we go with Variable trigger.

For start and end date.

Regards,

Pankaj

Re: Set analysis sum between two given dates

bvssudhakar — Thu, 09 Aug 2018 10:24:51 GMT

Hi Stefan,

I have some queries and i have posted in community also but no one are responding to my queries.

Can you help me for this query..:=Sum({<Floor(Today()-30)-Floor([Closed date])>}[No. of CV Submitted])

I wrote this expression for bar chart but it is giving error

My requirement is i want to calculate date difference from closed date to last 30 days for resumes submitted column

Please help me out from this

Thank you in advance

Re: Set analysis sum between two given dates

steve1982 — Fri, 28 Dec 2018 05:38:38 GMT

Hi Stefan,

If the startDate and EndDate as the dimensions in a table, which mean each rows contain different StratDate and EndDate, how this this can be done on set analysis?

Thanks
Steve