Qualifiers for Sum Expression

[cbaqir]

This seems like it should be so simple and yet I cannot figure out the correct qualifier for QlikView so I am turning to all the gurus in the Community... thanks in advance!

I need to show a metric of FTE Capacity. Simply, it should be sum(hours)/sum(month_cap). The user has the ability to select 1+ months. The Data Architect and I have approached this a couple of different ways with no working solution yet. We are trying to avoid adding a snowflake table with the month (reflected as the last day of a month) and hours.

Our FACT table has TRX_DATE which joins to the Calendar table.

1) PREFERRED: Added MONTH_CAP to calendar table. The issue is that the calendar table has an entry for every day and the FISCAL_MONTH_YEAR is in the table as the last day of the month (i.e. 12/31/14 = December 2014). The idea is picking one month should reflect the month_cap for that month (i.e. 160 for December 2014) and multiple months should have the sum of month_cap (I.e 320 for Dec and Jan which have 160 each).

The problem: Sum(month_cap) here results in 160 hours a month * 31 (the number of days in the month).

If Dec and Jan are selected, Sum(distinct month_cap) only results in 160 since Dec and Jan both have 160 hours in the month.

2) Added DAY_WORK_HOURS to calendar table so each day of work is reflected as 8 hours.

The problem here is that FISCAL_MONTH_YEAR is reflected as the last day of the month so sum(DAY_WORK_HRS) only results in 8 hours (or one day).

3) Added an additional subset of data to the FACT table by adding a new column called ‘App’ with a value of MonthCap and the only field populated is MONTH_CAP. The populated MONTH_CAP data by resource was set to null.

The problem here is sum( MONTH_CAP) is now not tied to my other datasets so my dimension filters don’t work.

Any suggestions would be appreciated - especially a simple qualifier for option 1.

Thanks again!

Cassandra

[Anonymous]

See if this is what you need.  I'm using expression

sum(if(FISCAL_MONTH_YEAR = aggr(max(TRX_DATE),FISCAL_MONTH_YEAR), MONTH_CAP))

[cbaqir]

Thanks, Michael. I assume this is for Scenario 1. I will give it a shot.

[Oleg_Troyansky · Accepted solution]

Well, the PREFERRED solution here depends on the size of your data... If you are dealing with a large dataset, then keeping the Month Cap in the Calendar is pretty bad, and adding a Snowflake table for every month makes things even worse. The issue here is having Measures (Hours and Month Cap) coming from different tables.

If you can afford that, then personally I'd recommend the snowflake solution - it's simpler and cleaner.

My second best choice would be a simplified version of Michael's solution:

sum( aggr(max(MONTH_CAP),FISCAL_MONTH_YEAR, <Your Chart Dimensions>))

This formula summarizes Month_Cap values, only using one value per Month. You should remember to add your chart dimensions to the list of AGGR dimensions, or else the formula won't work.

cheers,

Oleg Troyansky

Come and learn Set Analysis and Advanced Aggregation with me at www.masterssummit.com - take your QlikView skills to the next level!

[cbaqir]

Oleg,

Thanks! Can you give me an example of what you mean by "Your chart dimensions"? In some cases, I am just displaying the calc in a text box and in others, using it in a bar chart.

For example:

=num(sum(ADJ_CAP)/(sum( aggr(max(MONTH_CAP),FISCAL_MONTH_YEAR, <Your Chart Dimensions>))))), '#,##0')

[cbaqir]

Do you have any idea why these 2 expressions would not give me the same result? Am I missing a ()?

Sum([ACT_HRS])/$(vMONTH_HRS)
Sum([ACT_HRS])/(sum( aggr(max(MONTH_CAP),FISCAL_MONTH_YEAR, MONTH_CAP)))

vMONTH_HRS = (sum( aggr(max(MONTH_CAP),FISCAL_MONTH_YEAR, MONTH_CAP)))

[Oleg_Troyansky]

Hi Cassandra,

When you use this formula in a text box (no dimensions), then there is no need to add anything. In charts, simply add the Chart dimension to the list of your  AGGR dimensions, or else the formula won't return the correct result. So, for example, in a bar chart by Customer, the field Customer needs to be added to the list of AGGR dimensions.

cheers,

Oleg

[Oleg_Troyansky]

First, let me make a comment about the formula itself... You are calculating max(MONTH_CAP) inside an AGGR function with MONTH_CAP listed as one of the dimensions... In this case, each distinct value of MONTH_CAP will be the same as its own maximum. In other words, you are not going to get your max value. Instead, you will get all individual values one by one. In this case, MONTH_CAP should not be an AGGR dimension.

Regarding your question... in your variable definition, is it starting from an equals sign "=" ? If it does, then the expression is being evaluated once and then only the end result is being used in your chart. The other expression is being evaluated in every line of the chart.

If you post a sample, I could take a look...

cheers,

Oleg

[cbaqir]

Oleg,

Here is a sample of the chart and of the text box. Your help is greatly appreciated!

Regards,

Cassandra

[Oleg_Troyansky]

Hi Cassandra,

I'm attaching your sample with some of my additions here:

1. For the most part, you are using the formula correctly.

2. You don't need to repeat the Month as an AGGR dimension, one time is enough.

3. Don't include Month_Cap into the list  of AGGR dimensions.

4. Your main problem (I think) is the fact that your calendar is only partially full - only 42% of your Fiscal Months are described in the calendar. That distorts your calculation and charts that are driven by Fiscal Year and Month.

cheers,

Oleg Troyansky

Come and learn Set Analysis and Advanced Aggregation with me at www.masterssummit.com - take your QlikView skills to the next level!