Date Analysis

[akpofureenughwu]

Good day everyone,

Normally when I do my date analysis.. my date comes in this format  dd/mm/yyyy but I have a data set that has this format

Service Month	Invoice Number	Area	Invoice Value	VAT
01/01/2016	AA101	Area 1	1000	10
01/02/2016	AB201	Area 2	1400	14
01/03/2016	AW102	Area 2	3000	30
Excess and Excess Inv, 2016	AA121	Area 1	200	2
01/01/2017	AA110	Area 1	2300	23
Excess and Excess Inv, 2017	AE123	Area 2	1300	13

The Excess and Excess is more like financial activity that took place during the calendar year but the analyst can't pick the exact month (date). It's like an accrual concept.

Can you help me with the date creation?

Regards

Message was edited by: Akpofure Enughwure

[Anil_Babu_Samineni]

Good, Are you talking about transpose data. Will you explain the result set?

[tomasz_tru]

What output do you want in those problematic row? Maybe alt function?

ex.

alt( date#( ServiceM , 'DD/MM/YYY' ), [function for Excess and Excess Inv, ]

'NA' )

[akpofureenughwu]

I would love the month field to have this,  Month :January, February, March, ......., Excess and Excess Inv

Year : 2016,2017

Thank you for the response

[akpofureenughwu]

I'm not transposing

I would love the month field to have this,  Month :January, February, March, ......., Excess and Excess Inv

Thank you for r

[johanlindell]

LOAD If (Index ([Service Month], 'Excess and Excess Inv') > 0,

     MakeDate (Trim(SubField ([Service Month], ',', 2))),

     Date (Date# ([Service Month], 'MM/DD/YYYY'), '$(DateFormat)')

     ) as Date,

*;

LOAD * Inline [

Service Month;               Invoice Number; Area; Invoice Value;VAT

01/01/2017;                  AA110;          Area 1; 2300; 23

Excess and Excess Inv, 2017; AE123;          Area 2; 1300; 13

01/03/2016;                  AW102;          Area 2; 3000; 30

01/02/2016;                  AB201;          Area 2; 1400; 14

01/01/2016;                  AA101;          Area 1; 1000; 10

Excess and Excess Inv, 2016; AA121;          Area 1;  200; 2

] (delimiter is ';');

[johanlindell]

Date (Date# (Month, 'YYYYMM'), 'MMMM') as MonthName

should do the trick!