Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Not applicable

using formula in pivot table

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

5 Replies
MVP
MVP

Re: using formula in pivot table

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

/

((TGT_YEAR/12)*SRDAYS))

/

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

Not applicable

Re: using formula in pivot table

hi Manish,

thanks for replying.

i tried this but its not working.

also its showing error near BRNO (next to comma)

MVP
MVP

Re: using formula in pivot table

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Re: using formula in pivot table

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

Great dreamer's dreams never fulfilled, they are always transcended.
Not applicable

Re: using formula in pivot table

hi,

SRDAYS will vary for different branches.

i tried using aggr too.

but its not working