One expression with two different date dimensions.

[#1]

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

[Anonymous]

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.

[datanibbler]

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