Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Discover how organizations are unlocking new revenue streams: Watch here
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Nested Variable not Working In PiVot Table Expresssion

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.

1 Reply
Qrishna
Master
Master

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