topic Re: using formula in pivot table in QlikView

using formula in pivot table

Thu, 12 Mar 2015 17:16:18 GMT

HI EXPERTS,

I HAVE THE FOLLOWING FORMULA FOR A PARTICULAR FIELD IN A PIVOT TABLE.

((Sum({< [CalendarDate] = {'>=$(=Date(vThisMonthStart))<=$(=Date(vMaxSalesDate))'} >} SPAMT)

((TGT_YEAR/12)*SRDAYS))

(Sum({<CalendarMonthYear={'$(vReview1MonthYear)'}>}SRDAYS), BRNO))

but if i use this in the table, i m getting null values.

Please help me in this regard

Re: using formula in pivot table

MK_QSL — Thu, 12 Mar 2015 17:21:16 GMT

((Sum({< CalendarMonthYear = , [CalendarDate] = {'>=$(=Date(vThisMonthStart))<=$(=Date(vMaxSalesDate))'} >} SPAMT)

((TGT_YEAR/12)*SRDAYS))

(Sum({<CalendarDate = , CalendarMonthYear={'$(vReview1MonthYear)'}>}SRDAYS), BRNO))

Re: using formula in pivot table

Fri, 13 Mar 2015 09:01:21 GMT

hi Manish,

thanks for replying.

i tried this but its not working.

also its showing error near BRNO (next to comma)

Re: using formula in pivot table

jonathandienst — Fri, 13 Mar 2015 09:28:53 GMT

I expect your problem is in the TGT_YEAR and SRDAYS. These fields are not in an aggregation function and probably have more than one possible value, so they evaluate as NULL.

HTH

Jonathan

Re: using formula in pivot table

PrashantSangle — Fri, 13 Mar 2015 10:03:39 GMT

Hi,

(Sum({<CalendarMonthYear={'$(vReview1MonthYear)'}>}SRDAYS), BRNO))

what is logic behind writing , BRNO))

after Sum()
are you using any aggr()

Try writing each expression in 3 different text object.

Check which value giving you null.

Regards

Re: using formula in pivot table

Fri, 13 Mar 2015 10:15:12 GMT

hi,

SRDAYS will vary for different branches.

i tried using aggr too.

but its not working