topic Restricting Expressions regardless of other Dimensions in QlikView

Restricting Expressions regardless of other Dimensions

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Restricting Expressions regardless of other Dimensions

big_dreams — Mon, 21 May 2018 10:58:10 GMT

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

Re: Restricting Expressions regardless of other Dimensions

Anonymous — Mon, 21 May 2018 11:22:32 GMT

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.

Re: Restricting Expressions regardless of other Dimensions

Anonymous — Mon, 21 May 2018 11:51:03 GMT

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

Re: Restricting Expressions regardless of other Dimensions

tresB — Mon, 21 May 2018 12:56:06 GMT

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.

Re: Restricting Expressions regardless of other Dimensions

Anonymous — Mon, 21 May 2018 13:44:25 GMT

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)