Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm currently struggling doing to following calculation in my document. I have 2 different date dimensions (service_date & real_date). Service_date shows when the money was posted, real_date shows for what day the money posted. Real_date only appears when service_date is different to real_date. If service_date if equal to real_date, real_date remains empty. However, what I need to achieve is to add the real_date amount to the relevant service_date in my pivot table. Please see an example below. The -38 27th Apr real_date need to get added to the 320 27th Apr service_date.
Is it somehow possible to do that calculation in an expression keeping the pivot table format with only visible the service_date column? Many thanks in advanced.
Best regards
Andreas
HI Andreas,
I recommend you to do it in script, adding the real_date where empty.
LOAD *, if(len(service_date)>0 and len(real_date)=0, service_date, real_date) as real_dateOK
FROM yoursource;
Then, use the real_dateOK field in your expressions.
Hope it helps.
Marc.
Hi,
I don't do much work with pivot_tables - next to none actually, I try to stick to barcharts and combo_charts to keep things simple.
I know however that you can define expressions in a chart and, regardless whether or not you display them, you can refer to them (to their output) in other expressions.
So you have to make sure that the column "real_date" - display it just for testing, you can hide it once you are sure about that - is always filled. Then you can do any calculation with it.
You can copy one field into another - that's what you can do in any record where "real_date" is not filled. Build that into an IF_construct to query whether there is already something (in which case you should copy that) - use a 3rd field to do that.
HTH
Best regards,
DataNibbler