Connecting data to a month

[#1]

Hiya,

I have a problem here. I have a Excel sheet for each month - Jan - Feb - etc. In there i have a lot of invoice data. In Jan invoice it can be date's from both november and december etc.

I want to make a graph showing the Sum(Amount) for each month. But since there is different months in Jan it gets wierd.

So any ideas on how to connect the different Excel sheets to a month? I can not change the Excel sheet since they are automatic generated.

Hope you understand. I have only the personal license so can't update an example.

Thanks!

//Erik

[alexandros17]

Do not care about excel sheets, load them all, then, supposing that invoice date field is "Invoice_Date" add fields in each load:

LOAD

...

...

Month(Invoice_Date) as month,

year (INvoice_date) ad year,

...

From....

use year and month as dimension in the chart and it is done

[#3]

LOAD 

     Amount,

     Date(Date,'YYYY-MM-DD') as Date ,

     Month(Date) as Month

FROM

[\\xxxxxxx]

(ooxml, embedded labels, header is 5 lines, table is [Inv - 4113605620]);

The problem with you solution is that for January the total invoice is 10.000 Euro. But the dates in the sheet are from November and December.

So when i click Jan now with your solution i only get Sum(amount) for Jan which is not the same as the invoice for Jan.

Do you copy?

Thanks!

[alexandros17 · Accepted solution]

Now it is clear, the solution is not so different,

when you load add a dummy field (ex. DateSheet)

So:

Load

...

'201401' as DateSheet

...

when you load january and so on.

Now use DateSheet as dimension

Let me know

[Anonymous]

Hi Erik,

how do you get the Jan Amount on the invoice

from what i am getting the total for JAn accumulates from months prior to Jan right?

flag all invoice amount that affect the total for Jan  with an extra field eg "EffectiveMonth" which will be your Jan

example:

InvDate | InvAmout | EffectiveMonth

02/12/13 | 50000| 01/2014

so then on  your selection use EffectiveMonth

[#6]

Yes - it depends. But Jan can include Nov and Dec. Feb can include only Jan etc..

I can't not change the Excel files since they are autogenerated to a folder.

Regards

Erik

[Anonymous]

Hi Erik,

Did u have  a look at the illustration i send above does it make any sense to u?

[#8]

Yes - nice that worked.

Now i have another question, but maybe i will start a new thread.

The last one in the folder is 2014-08. How can i make it that it load 2014-09 when its created and also how can i make a automatic dummy field

Thanks.