Showing all values for a month, even days when no activity

[#1]

I am supposed to display a bar chart showing the activity for a product by each day. However, there are many days a month when there is no activity. I thought I could handle it like this:

1 - Create a INLINE table of "ActionDay" from 1 to 30

2 - In my script, I use the following  SQL code: DATEDIFF(Day,LicenseCreated,GETDATE()) As "ActionDay"

3 - I have "UNQUALIFY ActionDay;" as a line in my script.

In my chart, I have as the dimension "If(ActionDay<=30,ActionDay,Null())", and I have checked both Suppress When Value is Null, and Show All Values.

For my expression, I have "if(IsNull(Count(LicenseCreated)),0,Count(LicenseCreated))", and I have unchecked Suppress Zero Values on the Presentation tab.

Yet, I still see data for only those days when Licenses were created; the other days don't show up at all. I tried changing it to a table instead of a chart, and made sure "Suppress Missing" and "Suppress Zero" were UNchecked on the Presentation tab, and that didn't work either.

Any thought?

[Gysbert_Wassenaar]

No idea. ActionDay as dimension and Count({<ActionDay={'<=30'}>}LicenseCreated) as expression should work if you uncheck Suppress Zero-Values on the Presentation tab. You'll have to post your document (or an example that demonstrates the problem). Perhaps setting the Dimension Axis to continuous (on the Axes tab) helps.

[Anonymous]

Kevin,

This is something to resolve on the data modeling rather than chart expressions.  See attached.

Regards,

Michael

[Anonymous]

On the second thought - it should work without any tricks as well, also attached.

[#5 · Accepted solution]

Thanks, Michael. I stumbled on the following, and it works:

If(IsNull(aggr(count(CurrDocs.LRD_Created)+count(CurrLicense.LICD_Created),RegisterAge)),0,aggr(count(CurrDocs.LRD_Created)+count(CurrLicense.LICD_Created),RegisterAge))

Frankly, I thought I had what you showed in your second example, but it wasn't working until I added the "aggr" function.