count days in a month

[bittmapp]

Hello,

I have been able to create a count metric that counts the number of days in a month, but when I put an attribute from other dimension than time, there is a cartesian join with the lookup table of the un-related attribute. What I want to do is show a Month, products, and sales with the total number of days in the month, not the number of days that the product has data. Can this be done? Please help.

Like this

Month Product Sales # of days in month
------------------------------------------------------
May Shirts 100.00 31
May Socks 200.00 31
May Shoes 125.00 31

 

[teccum]

Try Enabling the metric "# of days in month" to outer join. This can be done from the menu Data -> Report Data Options --> Metric Join Type.

This issue is because, since you are having your sales fact in your fact table only the certain days in the May month. Since by default Mstr does a inner join to select the matching data for all those fields in your report. If this does not resolve your issue then you might have to create a aggregate table or have to populate your fact table for all the days in May with 0 sales transaction.

teccum.

 

[iolaper]

Try also defining a level metric by removing Report Level and putting in 'Month' attribute.
What I assume this should do is, based on the Month it should count the number of days and should remain unaffected by the data being pulled in.

Let me know if this works!