topic Re: Connecting data to a month in QlikView

Connecting data to a month

Tue, 30 Sep 2014 09:28:42 GMT

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

Re: Connecting data to a month

alexandros17 — Tue, 30 Sep 2014 09:32:57 GMT

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

Re: Connecting data to a month

Tue, 30 Sep 2014 09:40:34 GMT

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!

Re: Connecting data to a month

alexandros17 — Tue, 30 Sep 2014 09:48:13 GMT

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

Re: Connecting data to a month

Anonymous — Tue, 30 Sep 2014 09:49:46 GMT

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

Re: Connecting data to a month

Tue, 30 Sep 2014 09:56:42 GMT

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

Re: Connecting data to a month

Anonymous — Tue, 30 Sep 2014 09:59:44 GMT

Hi Erik,

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

Re: Connecting data to a month

Tue, 30 Sep 2014 10:14:33 GMT

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.