Nested Variable not Working In PiVot Table Expresssion

Wed, 08 Jul 2015 19:09:43 GMT

Hi Experts,

I am creating a Pivot Table field using following expression:

sum({<Data_Period={'YTD'},Formatted_Date={'$(vYTDNew)'}>}Chargeable)/sum({<Data_Period={'YTD'},Formatted_Date={'$(vYTDNew)'}>}Standard_Avail)

and the variable vYTDNew definition is:

=Date(IF($(vLastMonth)<=$(vMaxYTD),$(vLastMonth),$(vMaxYTD)),'dd/MM/yyyy')

The above expression is not working for me. However, if I replace nested variables with their actual definitions like below then it works fine:

=Date(IF(MonthStart(Date(MonthStart(Only(BaseData_Date))-1,'dd/MM/yyyy')) <=Max({<Data_Period={'YTD'}>} Date),MonthStart(Date(MonthStart(Only(BaseData_Date))-1,'dd/MM/yyyy')) ,Max({<Data_Period={'YTD'}>} Date)),'dd/MM/yyyy')

IO tested the variable vYTDNew using text object and it is returning the expected value.

Not sure why it does not work in the expression that I am using for creating pivot table field. Pelase help.

Thanks in advance.

Re: Nested Variable not Working In PiVot Table Expresssion

Qrishna — Wed, 08 Jul 2015 20:27:41 GMT

Try:

sum({<Data_Period={'YTD'},Formatted_Date={$(=vYTDNew)}>}Chargeable)/sum({<Data_Period={'YTD'},Formatted_Date={$(=vYTDNew)}>}Standard_Avail)

=Date(IF($(=vLastMonth)<=$(=vMaxYTD),$(=vLastMonth),$(=vMaxYTD)),'dd/MM/yyyy')

thanks

topic Re: Nested Variable not Working In PiVot Table Expresssion in QlikView

Nested Variable not Working In PiVot Table Expresssion

Re: Nested Variable not Working In PiVot Table Expresssion