Place the value when data does not exist to consecutive months data.

[#1]

Hi All,

           I need a report, where the date should be placed to consecutive month if that month data is zero or null.

For Example:

Data:

Month Sales

Jan    100

Feb     0

Mar  200

Apr     230

May     140

Jun     0

Jul     0

Aug 120

...

Etc

In this case For Feb month the value should be 100(Jan Data) and Jun and Jul should be replaced with May data (140), the same results should be appear in pivot table.

Please let me know in case you need any more information on it.

Regards,

Ravi.

[#2]

Hi,

I hope this helps u.

C u,

Stefano.

[Anonymous]

Hi Ravi,

You could also maybe solve it by using the Peek() function in your loading script.

Check this: http://community.qlik.com/message/154446#154446

Good luck.

Dennis.

[Anonymous · Accepted solution]

If this is your data in fe Excel:

Month  Sales

Jan  100

Feb

Mrt 200

Apr 230

Mei 140

Jun

Jul

Aug  150

Sep

Okt

Nov

Dec  200

You can use a load script like below:"

Directory;

LOAD Month,

     IF(ISNULL(Sales), Peek(Sales), Sales )as Sales

FROM

[example (1).xls]

(biff, embedded labels, table is Tab$);

This Will generate this:

Jan	100
Feb	100
Mrt	200
Apr	230
Mei	140
Jun	140
Jul	140
Aug	150
Sep	150
Okt	150
Nov	150
Dec	200

And you can use this in any object or charts