Assistance with Data Modeling or Expressions

[Quamie]

Hi All,

I'm a beginner and I'm having an issue with my data modeling, or my Expressions, or the way I've built this dashboard, or all three!

-------------

Issue: I'm unable to get data from my open reqs table and headcount table to effectively load together in a chart. (Headcount+Open Reqs = Projected Headcount)

I say effectively, because while I have developed an expression that works, it has an immense loading time, and sometimes fails to load (Out of object Memory).

Count({<MonthEnd_Flag={"1"}>}Distinct([Person Number]))+
Count({<ReqMonthEnd_Flag={"1"}>}Distinct(if(Text(Date(ReqDateField,'MMM')&'-'&Year(ReqDateField))=MonthYear,[Req. Identifier])))

The above produces the correct data, but as I said, the load times are immense. 

--------------

The purpose of the dashboard is to calculate some basic HR data, such as headcount, turnover, open requisitions, etc.

Here is my current data model;

The headcount data works fine (although load times are around 1.5 minutes due to creating unique date fields for each record, maybe there was a better way to do this?)

The main issue centers around integrating the OpenReqs table into the data module. I'm unable to map the OpenReqs table directly to the Calendar and Hierarchy, as it would cause a loop. I attempted to circumvent the issue by loading the OpenReqs table twice, one to connect it to the calendar, and the other to connect it to my hierarchy tables. 

I'm using the hierarchy and hierarchybelongsto functions to create a tree-view(s) of my desired data;

E.g;

I have a feeling that creating a _DateField record for each individual record (resulting in ~2 million records) is part of the problem. ☹️

Any insight would be appreciated, thank you for taking the time to look at my issue! 🙂 

[marcus_sommer]

You could try if:

rangesum(
   Count({<MonthEnd_Flag={"1"}>} Distinct [Person Number]),
   if(Text(Date(ReqDateField,'MMM')&'-'&Year(ReqDateField))=MonthYear,
      Count({<ReqMonthEnd_Flag={"1"}>} Distinct [Req. Identifier])))

improves your performance enough (avoiding the text() & ... might be also useful but is just the second step).

If not you will need to change the datamodel. Without knowing your data and all your requirements it's difficult to say what might be necessary. But I think I would start with concatenating from OpenRegs and Headcount (and/or respectively the OpenRegs2) into a single table with adjusting the fieldnames and by adding a source-field to be able to differ between the different sources.

- Marcus

[Quamie]

Hi Marcus,

I appreciate your reply!

Unfortunately upon testing your offered solution is not actually counting the OpenReq table data.

I guess to simplify the issue, 

When using MonthYear as a dimension on a chart, this will properly count the Reqs;

Count({<ReqMonthEnd_Flag={"1"}>}Distinct(if(Text(Date(ReqDateField,'MMM')&'-'&Year(ReqDateField))=MonthYear,[Req. Identifier])))

Your solution unfortunately does not;

if(Text(Date(ReqDateField,'MMM')&'-'&Year(ReqDateField))=MonthYear,
      Count({<ReqMonthEnd_Flag={"1"}>} Distinct [Req. Identifier])))

I don't know enough about qlikview to comment, maybe it has to do with having the aggregation function on the outside?

I will;

- Continue to attempt to get your solution to work with tweaks

- Otherwise take the time to update my post with a sample qvw and dataset.

-Explore your suggestion of concatenating the OpenReqs and Headcount into a single table with a source field. While pretty complex, this may well be the optimal solution.

Thank you again for your assistance, I'll follow up when I come to a better solution! 🙂 

[marcus_sommer]

My suggestion on the expression was to change:

count(if(condition, Field))
   into
if(condition, count(Field))

which is often interchangeable and much more performant - but both approaches are not absolutely identically. By count(if(condition, Field)) the condition will be checked for each single record which makes such an approach in general quite slow - especially if it's applied on rather larger datasets and in your case it needs to hop over multiple tables respectively the virtual table which is created in the background on which the calculation is performed could be quite large and even if most of the calculations are multithreading the creation of those virtual tables is a single-threading process.

Therefore my recommendation to consider a change on the datamodel.

- Marcus