topic problem in monthend function in QlikView

problem in monthend function

Fri, 20 Jun 2014 05:44:45 GMT

=Sum(If(REC_DAT<='$(vMonthEndDate)' and (Len(GR_DAT)=0 OR GR_DAT>='$(vMonthEndDate)'),INV_QTY))

variable vMonthenddate consists of all the monthend date in DD-MM-YYYY

this expression works well butif the selection is based on month but it is not working on year selection as its showing 0 as output

Re: problem in monthend function

tresB — Fri, 20 Jun 2014 06:17:35 GMT

What is the exact definition of variable vMonthEndDate?

Re: problem in monthend function

Fri, 20 Jun 2014 06:19:29 GMT

Date(MonthEnd(Date),'DD-MM-YYYY') as MothEndDate,

vMonthEndDate is =mothendDate

Re: problem in monthend function

tresB — Fri, 20 Jun 2014 06:57:54 GMT

If you select a year, the variable doesn't get assigned by a value, because it has many values(many monthend values for a year) in the scope then and your variable dfinition doesn't tell about which to be considered when many are there. I guess you have to use an aggregation function like:

vMonthEndDate = Date(Max(MonthEndDate), 'DD-MM-YYYY')

Re: problem in monthend function

Fri, 20 Jun 2014 08:32:28 GMT

the aggregation function displays all the months but I am getting the same values for all the months is there any problrm in the expression are can u find where the mistake is

Re: problem in monthend function

tresB — Fri, 20 Jun 2014 08:41:21 GMT

Hard saying without seeing your app and understanding the data. A first look at your expression tells me that may be you need AND rather than OR like:

=Sum(If(REC_DAT<='$(vMonthEndDate)' and (Len(GR_DAT)=0 AND GR_DAT>='$(vMonthEndDate)'),INV_QTY))

Re: Re: problem in monthend function

Fri, 20 Jun 2014 09:04:48 GMT

vMonthEndDate = Date(Max(MonthEndDate), 'DD-MM-YYYY')

this expression is displaying the maximum value that is present in the year and that value is being displayed in all the month fields, when i use the min instead of max it displays the minimum value for all the month fields