Missing values AND aggregations

[lzanetti]

Hi all,

workng with rolling sums I ran into an issue.

Say I want to have the volume of sales 1 year backwards, month by month.

I used:

RangeSum(

After(

    total  Sum( {< [Posting.Date.autoCalendar.YearMonth] = >} Quantity)        , 0,12 )

        )

The problem is that if I have missing values (sales = 0 in one month) this formula gives me the sum over 13 months (because that month is not listed on the table, so 12 steps backwrd is 13 months).

Does anyone know how to work around this?

Want I would like is that the sum goes back 1 year even if there are months not listed (e.g. 2/2018 to 3/2017, 1/2018 to 2/2017, 12/2017 to 1/2017)

Thank you very much

[sunny_talwar · Accepted solution]

The best way to resolve this issue is to use The As-Of Table

[lzanetti]

Hi Sunny,

I read the article. This means I have to create even a Master calendar to link the As Of table to?

I'm not confident with scripts, do I have to put the script as a new section in the data load editor?

Thanks so much

[sunny_talwar]

You don't need a master calendar to get this resolved, but having a master calendar will def. help. Yes this will go in the script

[lzanetti]

I'm confused with this.

I have a table called "Shipped_2011inPoi", loaded from a "Shipped_2011inPoi.csv" file.

I found this tutorial that would help me a lot:

http://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/

but I get an error I don't understad.

[DateLink]:

LOAD

[Product.hierarchy]

    ,Date([Posting.Date]) as Date

    ,'Shipped' as DateType

RESIDENT Shipped_2011inPoi

;

this instruction gives ERROR: table Shipped_2011inPoi not found.

But that table is loaded and working.

Does anyone see what the problem is?

PS: this happens for every other resident table I try to load from

Thanks

[sunny_talwar]

How can the error be spotted by just looking at this?

Either share the script before this, or share your qvw file for us to take a look at

[lzanetti]

You're right Sunny, I understand.

I managed to work around this issue and now my app works (I added the new script before the auto generated one, so the tables were not available while trying to create the master calendar).

Now, going back to the first question (and answer) I gyuess I have to use an As-of-table to have YearMonth and YearQuarter available in tables and plots, haven't I?

[sunny_talwar]

Yes, I would believe so...

[lzanetti]

Thanks Sunny, I managed to create an  As-of-table and it works.