Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a couple of tables in my Data model that looks like as it is below. And by means of the same field name, the two tables will be associated with 'Date'.
FACT Table:
Date | Name | Value |
---|---|---|
2017-12-31 | A | 40 |
2018-01-01 | NULL | NULL |
2018-01-02 | A | 10 |
2018-01-03 | NULL | NULL |
2018-01-04 | B | 20 |
Master Calendar:
Date | Month | Business Day Indicator |
---|---|---|
2017-12-31 | Dec | Y |
2018-01-01 | Jan | N |
2018-01-02 | Jan | Y |
2018-01-03 | Jan | Y |
2018-01-04 | Jan | Y |
My Front End Usecase:
I will be showing a Straight Table with 'Name' and 'Month' as Dimension and Sum(Value)/ Count(Business Days) as Expression. For clarity, I am putting Sum(Value) and Count(Business Days) as two separate expressions.
This how my Straight Table should look ideally.
Name | Month | Sum(Value) | Count( {<Business Day Indicator = {'Y'}>} Date) |
---|---|---|---|
A | Dec | 40 | 1 |
A | Jan | 10 | 3 |
B | Feb | 20 | 3 |
But it looks like this.
Name | Month | Sum(Value) | Count( {<Business Day Indicator = {'Y'}>} Date) |
---|---|---|---|
A | Dec | 40 | 1 |
A | Jan | 10 | 2 |
B | Feb | 20 | 2 |
Here, the problem lies in the fact that no 'Name' has a 'Value' for 2018-01-03, which falls under Business Days. How do I make sure the Count( {< Business Day Indicator = {'Y'} >} Date) will count all the Business Days available in my Master Calendar?
so basically if Name field is null then you want to assign that day to all available Name??
try below
Fact:
Load Date,Value from factTable
where len(trim(Name))=0 or Name='Null' or isnull(Name);
join
Load distinct Name from factTable;
concatenate
Load Date,Value from factTable
where len(trim(Name))<>0 or Name<>'Null' or not isnull(Name);
......
// add as your remaining code as it is.
..
Regards
Hi,
Thanks for your response. I forgot to mention an important point. My bad. The Starting and Ending Date selection is at the discretion of users. So, the business days changes based on the User Selection.
Whereas if we do it in the back end like you mentioned, I get Static Business days for all the Months.
Example: If User selects his range as 31st Dec 2017 to 4th Jan 2018, I get the Business days as I mentioned. While using the technique you mentioned, I get the Business Days as more than 20 for Jan and Dec.
tresesco Hi, it would be of great help, if you know a way to achieve this.
Hi,
I could not reproduce the output as you described.
I would request you to create a sample app and share to work upon mentioning the selection scenarios for better understanding.
Maybe you can create an island Master Calendar (with 2 filds: DateCalendar and Business Day Indicator]), with no connection to your data model, and then on the counting formula inserting:
Count( {<[Business Day Indicator] = {'Y'}, DateCalendar=p(Date)>} DateCalendar)