Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Restricting Expressions regardless of other Dimensions

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:

DateNameValue
2017-12-31A40
2018-01-01NULLNULL
2018-01-02A10
2018-01-03NULLNULL
2018-01-04B20

Master Calendar:

DateMonthBusiness Day Indicator
2017-12-31DecY
2018-01-01JanN
2018-01-02JanY
2018-01-03JanY
2018-01-04JanY

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.

NameMonthSum(Value)Count( {<Business Day Indicator = {'Y'}>} Date)
ADec401
AJan103
BFeb203

But it looks like this.

NameMonthSum(Value)Count( {<Business Day Indicator = {'Y'}>} Date)
ADec401
AJan102
BFeb202

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?

5 Replies
big_dreams
Creator III
Creator III

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

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

tresesco‌ Hi, it would be of great help, if you know a way to achieve this.

tresesco
MVP
MVP

Hi,

I could not reproduce the output as you described.

Capture.JPG

I would request you to create a sample app and share to work upon mentioning the selection scenarios for better understanding.

Anonymous
Not applicable
Author

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)