Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
lzanetti
Contributor III
Contributor III

Missing values AND aggregations

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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

8 Replies
sunny_talwar

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

lzanetti
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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?

Capture.PNG

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

lzanetti
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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