topic Re: Current Month MTD+Previous Month Calculated YTD in QlikView

Current Month MTD+Previous Month Calculated YTD

Thu, 05 Jan 2017 12:05:11 GMT

Hi All,

We are facing difficulty in implementing below logic. Please help.

Here is the logic:

For each month we are receiving the source file.

We have below source file for Aug 2016:

Entity	MonthYear	MTD	YTD
ABC	Aug-16	10	10
ABC	Aug-16	20	35
ABC	Aug-16	30	20

Source file for Sep 2016:

Entity	MonthYear	MTD	YTD
ABC	Sep-16	10	0
ABC	Sep-16	30	0
ABC	Sep-16	40	0

Output for Sep 2016:

Entity	MonthYear	MTD	YTD
ABC	Sep-16	10	20
ABC	Sep-16	30	65
ABC	Sep-16	40	60

We are not facing any issues in implementing MTD logic as it has different calculation.

But YTD logic we are not getting the desired output.

For Ex: YTD for Sep = MTD of Sep from source file + YTD of Aug month.

For the first month the logic works correctly as we are taking YTD value from source file directly. But from the next month onwards it does not works correctly and instead of taking calculated YTD value it take from source file.

Source file for OCT 2016:

Entity	MonthYear	MTD	YTD
ABC	Oct-16	40	0
ABC	Oct-16	60	0
ABC	Oct-16	80	0

Output for Oct 2016:

Entity	MonthYear	MTD	YTD
ABC	Oct-16	40	60
ABC	Oct-16	60	125
ABC	Oct-16	80	140

Please help in implementing it. Any help is much appreciated!!!

Regards

Deepak

Re: Current Month MTD+Previous Month Calculated YTD

sunny_talwar — Thu, 05 Jan 2017 12:23:37 GMT

Would you be able to share a sample file where you are working on this?

Re: Current Month MTD+Previous Month Calculated YTD

Thu, 05 Jan 2017 12:36:44 GMT

Hi Sunny,

Not able to share ....Sorry

Regards

Deepak

Re: Current Month MTD+Previous Month Calculated YTD

sunny_talwar — Thu, 05 Jan 2017 12:56:43 GMT

okay

Re: Current Month MTD+Previous Month Calculated YTD

petter — Thu, 05 Jan 2017 13:25:24 GMT

To get the logic to work properly we need to generate a unique row id so we can do a lookup into previous loaded rows using the Lookup() function in the script. This unique row id must be persisted for the table as a new field as long as the script runs.

Re: Current Month MTD+Previous Month Calculated YTD

Thu, 05 Jan 2017 13:40:25 GMT

Thanks Petter for reply!!

What exactly lookup function do here ??

For the Unique we have one more Column which is Unqiue i.e GLAccount we can use that column for the Unique ID than there is no use of Recno() function.??

Regards

Deepak

Re: Current Month MTD+Previous Month Calculated YTD

petter — Thu, 05 Jan 2017 14:55:47 GMT

Yes you are right - you should use a column that already is unique instead of the synthetically produced UniqueID that I made - then you will not need to use the RecNo() function.

Lookup() is to find the old YTD value from previously loaded files or previously loaded values for the same field in the current load statement.

Re: Current Month MTD+Previous Month Calculated YTD

Fri, 06 Jan 2017 10:01:57 GMT

Hi Petter,

Thank You for the help!!!

Above code works fine for first two months. The moment I add other month data, YTD logic does not works fine. Currently we have loaded three month data you can find as attachment...

Aug month YTD displayed as blank.

Attached here the source file for three Months and .qvw file.

Thanks a lot for the support!!!

Regards,

Deepak

Re: Current Month MTD+Previous Month Calculated YTD

petter — Fri, 06 Jan 2017 10:21:09 GMT

Make sure that your files are named so they are processed in the right order.

The logic in the load script demands that the ABC* files come in ascending order when it comes to file names.

So you should have files being named like:

ABC01 - for January

....

ABC12 - for December

Just renaming your file ABC1.txt to DATA-08.txt and ABC2.txt to DATA-07.txt will make the load script work without a problem. I just tested it.

Re: Current Month MTD+Previous Month Calculated YTD

Fri, 06 Jan 2017 10:48:35 GMT

Hi Peter,

Thanks for Reply !!

Since we have data for last 5 years that's why we have used ABC * for loading the files because we want dynamic load for every month.

Regards

Deepak

Re: Current Month MTD+Previous Month Calculated YTD

petter — Fri, 06 Jan 2017 11:52:14 GMT

So making a naming standard/convention for your files would be a useful thing then.

If I may - I would suggest something along the lines of this:

aaa-YYYY-MM.TXT -> SALES-2016-01.TXT

Then all files will be read in the right order... After processing the files one could rename or move the files to a history folder og mark them as processed.

You could then simply do:

LOAD * FROM SALES-*.TXT;

Using the wildcard * will get/read the files in sorted order - which means the months and years will be processed in the order they need to.

Re: Current Month MTD+Previous Month Calculated YTD

petter — Sun, 08 Jan 2017 09:13:53 GMT

I think I answered your question so please mark this thread as answered unless there are specific things in the answer that might be unclear.

Re: Current Month MTD+Previous Month Calculated YTD

Tue, 10 Jan 2017 12:54:51 GMT

Hi Petter,

There is one more condition for YTD , In each month some of new GL account is added in the Current Month File and which is not present in previous Month file, for those GL accounts MTD is equal to YTD. In your solution it is not working because you put the condition on GL accounts which is presents in both the files i.e Current Month File and Previous Month File.

for example: GL account 800005 is present in July File and which is not present in June than .YTD for this GL account is equal to MTD.

Please suugest a way how to put this new condition in Lookup function.

Regards

Deepak