How can I get the count of days for every month using Formulate (Custom column)?
I need to get the count of days in each month using the custom column option in a formula or any other ways in Discover. Does anyone have a solution for this?
8 replies
-
Hi ,
This very easy. I am assuming you have Date, Full Month, Year type attributes in your model.
Create a new Formulate formula against your model. Drag on the Count block from the Aggregate category. For the list select all your dates.
I'm assuming you want this as a Measure and have dynamically change according to its context, so in the DataPoint argument, choose any attribute marked as Time. In my case I'm simply choosing Date again. Click on the fx icon to choose a functional selection (green box below) and select CurrentMember,
Save the calculation into the Measures dimension.
Put the Measure on columns and choose ant Time attribute for the rows. In this case I have used the Date Hierarchy to show how the count of days is evaluated at any level or for any Time categorised attribute. This will also be in effect if you are filtering by a Time based attribute.
Hope that helps.
Ian
-
If you want to use a custom column to drive the measure, simply select the date attribute and create a Measure against it using Distinct Count as the aggregation method and hide the attribute:
My man in the Dev teams says this can be more efficient as it pushes the calculation down to the underlying database.
Ian
-
Hi
I have followed your solution before, and it worked well. However, I have a new requirement.
I need to get the days average based on the overall count/days count in a month. While the distinct count works fine without any filters, the days count decreases when other filters are added. I need a static days count for each month, including the leap year concept for February.
Notes: I want to achieve this without touching the model.
Can you help with this?
Thanks
-
Do you have a separate table for dates or are the date columns in the main fact tables (so - are they filtered by the age or not)? -
Hi ,
We don't have separate tables for the dates . We can create separate tables for dates in the model, but we don't have access to the model. Is there any solution for creating date tables in Formulate?
Thank you.
-
-
Hi ,
This is a function of deriving your Date dimension from your fact table. As soon as you filter the fact table, the dates will be filtered as well, so your count will reflect the number of dates that the item exists for in the table. Sometimes this is what hou want and sometimes it isn't. This why an independent Date dimension is important.
But we have what we have so let's see how to workaround this.
In fact, it's pretty straight forward. We need to "Lock" the date count to ALL items in the dimension you are filtering with. I.e. ignore any dimensionality introduced by the filter.
Create a new measure, use the Count of Days Measure, but select ALL for the dimension you are filtering with, i nthis case Product Sub Category:
Then use this Measure in your Query:
We have added a background filter (as you did) and we can see this is filtering my Count Days Measure (red box), but my Count Days All Prods is unaffected.
Of course, if there is a date that does not exist in your fact table, then it simply won't appear in the count, so this is NOT a fool proof method of generating a count of the days in every month.
Hope that helps!
Ian
-
Hi
I used the custom column to assign the day count for every month and I have applied the MOD function to find the leap year for feb month.
I got the output as like the below image .
I used the created formula in the discovery and added values in the drop zone. And got the expected output .
Is the steps I followed are fine ? (
)