topic Re: Expression Not Working in pivot table in QlikView

Expression Not Working in pivot table

NavinReddy — Sun, 27 Mar 2016 12:13:44 GMT

Hi Dear Experts,

if(Count({<Month=,RAG_RATING_CD={'R'}, MonthYear={">=$(=date(MonthStart($(vMonth)),'MMM YY'))<=$(=Date($(vMonth),'MMM YY'))"}>}RAG_RATING_CD)=1

and

Count({<Month=,RAG_RATING_CD={'R'}, MonthYear={"$(=Date($(vMonth)-10,'MMM YY'))"}>} RAG_RATING_CD)=1

and

Count({<Month=,RAG_RATING_CD={'R'}, MonthYear={"$(=Date($(vMonth)-50,'MMM YY'))"}>} RAG_RATING_CD)=1,

Count({<Month=,RAG_RATING_CD={'R'}, MonthYear={">=$(=date(MonthStart($(vMonth)),'MMM YY'))<=$(=Date($(vMonth),'MMM YY'))"}>}RAG_RATING_CD))

I have one expression its not working in pivot table, the same expression working fine in list box

i have used same dimension in pivot and list box

can some one help me what is the error, for reference please find below image

Thanks In Advance

Niranjan

Re: Expression Not Working in pivot table

sunny_talwar — Sun, 27 Mar 2016 12:16:12 GMT

May be this:

=Aggr(

if(Count({<Month=,RAG_RATING_CD={'R'}, MonthYear={">=$(=date(MonthStart($(vMonth)),'MMM YY'))<=$(=Date($(vMonth),'MMM YY'))"}>}RAG_RATING_CD)=1

and

Count({<Month=,RAG_RATING_CD={'R'}, MonthYear={"$(=Date($(vMonth)-10,'MMM YY'))"}>} RAG_RATING_CD)=1

and

Count({<Month=,RAG_RATING_CD={'R'}, MonthYear={"$(=Date($(vMonth)-50,'MMM YY'))"}>} RAG_RATING_CD)=1,

Count({<Month=,RAG_RATING_CD={'R'}, MonthYear={">=$(=date(MonthStart($(vMonth)),'MMM YY'))<=$(=Date($(vMonth),'MMM YY'))"}>}RAG_RATING_CD)), Metric)

Re: Expression Not Working in pivot table

Kushal_Chawda — Sun, 27 Mar 2016 12:26:22 GMT

=sum(aggr(if(Count({<Month=,RAG_RATING_CD={'R'}, MonthYear={">=$(=date(MonthStart($(vMonth)),'MMM YY'))<=$(=Date($(vMonth),'MMM YY'))"}>}RAG_RATING_CD)=1

and

Count({<Month=,RAG_RATING_CD={'R'}, MonthYear={"$(=Date($(vMonth)-10,'MMM YY'))"}>} RAG_RATING_CD)=1

and

Count({<Month=,RAG_RATING_CD={'R'}, MonthYear={"$(=Date($(vMonth)-50,'MMM YY'))"}>} RAG_RATING_CD)=1,

Count({<Month=,RAG_RATING_CD={'R'}, MonthYear={">=$(=date(MonthStart($(vMonth)),'MMM YY'))<=$(=Date($(vMonth),'MMM YY'))"}>}RAG_RATING_CD)), PivotTableDimension))

Re: Expression Not Working in pivot table

sunny_talwar — Sun, 27 Mar 2016 12:28:50 GMT

From the looks, OP seems to be using this as a dimension. But you are right, if this is an expression, then he might need to add out aggregation such as Sum

Re: Expression Not Working in pivot table

Kushal_Chawda — Sun, 27 Mar 2016 12:30:22 GMT

Yes, Over sum have to be there to display the total

Re: Expression Not Working in pivot table

NavinReddy — Sun, 27 Mar 2016 19:16:12 GMT

Thank you both of you its working

Best Regards,

Niranjan