Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Quamie
New Contributor II

Assistance with Data Modeling or Expressions

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. 

Qlik5.png

 

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

 

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;

Qlik2.PNG

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;

Qlik3.png

Qlik4.png

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! 🙂 

Tags (2)
Labels (2)
3 Replies
MVP & Luminary
MVP & Luminary

Re: Assistance with Data Modeling or Expressions

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
New Contributor II

Re: Assistance with Data Modeling or Expressions

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! 🙂 

 

 

MVP & Luminary
MVP & Luminary

Re: Assistance with Data Modeling or Expressions

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