2 dates from different table into 1 calendar dimension in Pivot Table

[dannyt1834]

Hi,

I am currently trying to plot an expression into a Pivot Table and show monthly MTD values. I have the following expression below. And I also included the variable value below.

I am having problem plotting and showing all the MTD values for each whole year. Which I reckon is due to the "Month" dimension which belongs to the "RoomStats" table as seen in the second part of the expression. As there are 2 tables being used which is "Transactions" and "RoomStats", the calculation for Transactions is not working properly and is only showing same values each month.

vDate.Reporting=Date(Only(AsAtDate), 'D/MM/YYYY')

sum({<TransactionDatasource={'Transactions'}, [Spend Code Class]={'Room Revenue'}, TransactionDay={'$(vDate.Reporting)'}>} TransactionAmountNoTax)

/
sum({<RoomStatisticsDate={'$(vDate.Reporting)'}>}RoomStatisticsOccupiedCount)

Thank you in advance. Been trying to work it out.

[Brett_Bleess]

Dan, I am not expert on these, but have a look at the following Design Blog post, I think it may be what you need in this case, but I am not certain.  There are other related posts in this area, so feel free to search yourself using the second link if the first one does not do the trick or help.

https://community.qlik.com/t5/Qlik-Design-Blog/The-Master-Calendar/ba-p/1471527

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett