Set Analysis Multiple Tables.

[ruzvmun]

Hey Guys,

First post!! This is really a long shot, I hope I ask this correctly. I have 2 tables, one Sales another Bus_Days_Calendar. 

The relationship between these tables is 'Date' and they are loaded individually in code therefore using QV joining.

Bus_Days_Calendar has a field 'Number_Business_Days' and is only populated for the first business Day of each month only, rest is 0. It shows the number of business days for that respective month.

I am trying to create a chart that shows average daily sales per month using the this field.

However QV is creating an inner join between the 2 tables such that it only populates the 'Number_Business_Days' when 'Date' matches between the 2 tables. So we have scenarios where the months are blank because they don't match between the 2 tables i.e no sales on 'First_business_Day' in the sales table.

The expression on the chart looks something like this:

(sum({<[Date] = {'>=$(vDate1) <=$(Date2)'}>} [Sales])
/
sum({<[Date] = {'>=$(Date1) <=$(Date2)'}>} Number_Business_Days)) /1000000

I tried doing this by month and year but because we have multiple 'Dates' in the Sales table, I end up having duplicates on the Number_Business_Days therefore throwing off the numbers.

Do you guys know how I can go around this?

Appreciate any help provided. 

[Vegar]

What is the content of your variables? Are you using different variables in the two expressions or is it just a typo in your post? Does your work day table only contain the two fields or do you have more info in it? Are you presenting you data per date /month in your chart objects? 

You could try adding the full set {1} to your denominator like below. Even possible to use Monthstart in your first date comparison. 

(sum({<[Date] = {'>=$(vDate1) <=$(Date2)'}>} [Sales])

/

sum({1<[Date] = {'>=$(=monthstart($(Date1)) <=$(Date2)'}>} Number_Business_Days)) /1000000

 Please try and let us know what happens. 

[ruzvmun]

Thanks for the feedback, I tried the above, but I think I am missing something coz its not working. Anyhow, I have attached a sample of what the data looks like.

Again Calendar table only shows value for Number_Business_Days on the first business day of the month. I have also attached the expected chart.

If you look at it, one of the products is missing values for all the months except for June. That's because we don't have sales on the 'first_business_day' in these months. 

I hope this helps explain.

Thank you for your help 🙂

[Vegar · Accepted solution]

Try this expression.

sum(Sales_Amt/100000)
/sum(total <Month_Short> Number_Business_Days)

See attached qvw.

[ruzvmun]

This is good, I appreciate the prompt response. Works like a charm.

I did notice one thing though, my calendar table has 3 years worth of data..as such the 'business_days' value is multiplied by 3, therefore scewing the data.

I did a workaround by adding max instead of total on the bottom calculation:

sum(Sales_Amt/100000)
/max(total <Month_Short> Number_Business_Days)

Do you think this is the best way out.

I apologize my dataset was incomplete obviously, imagine we have sales from 2018-2020 as well in the calendar table.

Thanks again for your time on this.

[ruzvmun]

Update:

I  got a solution; so I updated my code for the calendar table and have business days value showing for every date in the month.

Then I tried the below:

 (sum({$<[Date] = {'>=$(vStart_Date) <=$(vPrev_Date)'} >} [Sales])
/
 Max({$<[Date] = {'>=$(vStart_Date) <=$(vPrev_Date)'} >} Business_Days) )

Validated the numbers and looks good.

Thanks for your assistance, much appreciated