Show 0 if dimension does not exist in the selected period

[felipe_oliveira]

Hi Masters,

I have two tables, one with the actual numbers and one with the monthly “Plan”.

In the attached sample, I was expecting to have $1,800 in the “Plan” for Asia-Tech when I select the first 6 months of the year.

However, because there is no “Actual” data in March, it also skips the data for the “Plan”.

In a nutshell if I select only March, I would like to have zero as actual and $160 as “Plan” or Asia-Tech.

Please advise

Thanks

[hallquist_nate]

When I look at your Data model, I see that you are keying the Master Calendar to the Actual table with the %Date of Funding field.  I looked at the Date of Funding in the Actual table and there is now value for March, so yeah, it won't show anything.  My recommendation would be to concatenate your Actual and Plan into one fact table, then use the Master Calendar dimensions in your Pivot and see if that works better.  In that construct, you should be able to get a match from the Plan table to the calendar, and your Actual will be null.  In order to get a Date Key in Plan, just create a date with Month and Year, and then code the day to the first of the month.  I have used this in the past, and it works out pretty well.  If you need help on the code, you'll need to attached the data files, so I have something to reload, to get it all to work.

Good Luck,

Nate

[felipe_oliveira]

Thanks Nate!

I have attached the data file so you can help me with the code.

[hallquist_nate]

Attached is how I would build the app.  I gave you a Master Calendar in the app.  My advice to you is to copy this out into a text file and keep it handy.  A Master Calendar can solve a ton of challenges.  this one is pretty robust, but you can add to it if you like.  

I concatenated your two fact tables, then gave like names to like fields, so the script will create a nice clean fact table.  Also, I created a record type field.  I sometimes use this in Set analysis.  An example would be when your Plan amount and Actual Amount are just named "Amount".  This will differentiate the two different records.  Also, in large tables, this is really handy for data validations.

Good luck.

Nate

[Brett_Bleess]

Felipe, here are a couple of other Design Blog posts that may help in addition to what Nate gave you.

https://community.qlik.com/t5/Qlik-Design-Blog/The-Master-Calendar/ba-p/1471527

https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578

Regards,
Brett