Calculating sales measure when measure spend is not zero

[Spiller]

Hi all,

For the dashboard I am creating I need to reproduce a chart and table created by the business in their local Qlik Sense environment. It needs to check the budget and sales per business/program/project.

The sales should only be calculated when there is budget on project level. A simple if statement would make it work on row level, but the totals will not work then. How that best solve this in one calculation? As budget and sales are not separate fields, but both based on the logic below.

Budget
Sum({$< Version = {'BUDGET'}      
,VersionName = {'$(=Only([Budget Version]))'}
      >}ValueCurrency)

Sales
Sum({$< VersionName = {'Sales'}
       ,Version = {'Business Case'}
      >}ValueCurrency)

Thanks in advance,

Spiller

[stevedark]

Hi @Spiller 

If I understand what you are requiring correctly, you need to use the P() set analysis function, which gives you results where there is a possible match with another field, which can be based on different set analysis:

Sum({$< VersionName = {'Sales'}
       ,Version = {'Business Case'}
       ,Project=P({1<Version = {'BUDGET'},VersionName = {'$(=Only([Budget Version]))'}>}Project)>}ValueCurrency)

That should do it.

Typically when comparing to when a budget is available you will also want to do the same as is done on the Project above based on dates that budget is available for, so perhaps ,Month=P({1< etc.

Hope that helps.

Steve

[Spiller]

Thanks, that's in the good direction, but not working yet. I see that I overlooked that the sales or on program level and the budget on project level. Does that make a difference?

[stevedark]

Hi @Spiller 

When you say it is in a good direction, are you now able to only get the Sales for Projects for which you have Budget?

It is common that budgets are done to a higher level of granularity to sales. As long as Programs can be aggregated up into Projects you should have no problems, just ensure that you don't use Program as a dimension when doing comparison to budget, or it simply won't work.

If Sales don't currently have a project stamped on them you will need to work out how to deal with that in your data first. Can you, for example, ApplyMap a Project onto the Sale based on the Program?

Steve