topic Aggr() function - Usage of CPU Cores in QlikView

Aggr() function - Usage of CPU Cores

manojkulkarni — Wed, 04 Nov 2015 09:01:45 GMT

Hi All,

I am having a calculated Dimension, wherein I am using Aggr() function. Overall Data size is around 200M records. Systme configuration is 16 Cores, 128GB RAM. I think memory & cpu is not an issue. It is about utilization of resource.

To populate the chart (straight tale), qv is taking almost 120secs. I cross checked at resource utilization, only few of cores being used while calculating the chart dimension.

What could be the reason ? How to optimize the aggr() function ? Below is the calculated dimension. All the fields being used are from a single table.

=if((aggr(sum({<Year=,Month=>}if (user_txn_date>=(v_m12_date2) AND user_txn_date<= (vSelectedMonthDatePrevious),bill_amount)),user_id))=0,'No Purchase',

if((aggr(sum({<Year=,Month=>}if (user_txn_date>=(v_m12_date2) AND user_txn_date<= (vSelectedMonthDatePrevious),bill_amount)),user_id))<vLowerAmtLimit,'< '&vLowerAmtLimit,

if((aggr(sum({<Year=,Month=>}if (user_txn_date>=(v_m12_date2) AND user_txn_date<= (vSelectedMonthDatePrevious),bill_amount)),user_id))<vUpperAmtLimit,vLowerAmtLimit& ' - '&vUpperAmtLimit,

if((aggr(sum({<Year=,Month=>}if (user_txn_date>=(v_m12_date2) AND user_txn_date<= (vSelectedMonthDatePrevious),bill_amount)),user_id))>=vUpperAmtLimit,'>='&vUpperAmtLimit))))

Re: Aggr() function - Usage of CPU Cores

Kushal_Chawda — Wed, 04 Nov 2015 09:15:10 GMT

have you tried moving this calculation in Expression instead of Calculated dimension?

Re: Aggr() function - Usage of CPU Cores

punitpopli — Wed, 04 Nov 2015 09:29:34 GMT

Hi Manoj,

I think the P() can be used. can you please try with the same?

Thanks,

Punit

Re: Aggr() function - Usage of CPU Cores

marcus_sommer — Wed, 04 Nov 2015 10:44:10 GMT

Your nested if-loop with aggr-functions is a first class performance-killer. Even if aggr could be excuted in multi-threading it would be need a lot of ressources and response quite slowly by large datasets.

A quite easier approach might be to use the class() function to create (variable) buckets - it's not excactly the same what do you doing now but the insights which the user could get is quite similar.

If this is really no option than you need to reduce the number of calculations within your expression and each possible complexity. This could be reached with a kind of lookup-function which takes the result from the aggr-part (which needs to be calculate only once) and returned the lookup-value.

I have done similar things before and think this method could be adapted even if your checking against variables is more complicated: Re: Substitute to nested ifs

And in your case a solution might be built in this way:

pick(match(

ceil(aggr(sum({<Year=,Month=, user_txn_date = {">=v_m12_date2<=vSelectedMonthDatePrevious"}>} bill_amount),user_id)),

0,1,2,3,4 ......),

'no Purchase', $(=repeat('< ' & vLowerAmtLimit, vLowerAmtLimit)), ....

Challanges are to find an appropriate rounding-algorithm which will be needed for the lookup-matching and for the repeating-function inside the $-sign expansion - to create such lists with a correct syntax isn't easy and will need some attempts. If your limit-variables aren't complete variable - maybe restricted with a drop-down list, it could be easier to use fixed lookup-lists which are choosen through the limit-variables, too.

Further I have your inner if-checking replaced with a set analysis syntax - to mix up both if and set analysis is possible but most not necessary and set analysis will be mostly (a lot) faster.

- Marcus

Re: Aggr() function - Usage of CPU Cores

manojkulkarni — Wed, 04 Nov 2015 12:31:21 GMT

Thanks Marcus_Sommer, for your suggestion. it makes sense.

Didn't understand the functionality of '$(=repeat' in your expression. Please could you elaborate more. How to interpret in the above condition ?

Re: Aggr() function - Usage of CPU Cores

marcus_sommer — Wed, 04 Nov 2015 13:21:38 GMT

Repeat('string', 'number') repeats a string as often as defined within the second parameter ('number'). In your case with a pick(match()) - lookup you don't know how many times you need which string-return to place in because of the use from the variables.

Example (simplified) with variable-values and how the loopup return-list needs to look like:

var: vLowerAmtLimit = 2

var: vUpperAmtLimit = 5

pick(match(Expression, 0,1,2,3,4,5,6,7,8),

'no Purchse', '< vLower', 'vLower - vUpper', 'vLower - vUpper', 'vUpper', '> vUpper', '> vUpper', '> vUpper')

The $-sign expansion $(=Expression) will be needed because no expression would be directly accepted as parameter to pick(match()) so it needs to be calculated before - see here how it worked: The Magic of Dollar Expansions.

- Marcus

Re: Aggr() function - Usage of CPU Cores

manojkulkarni — Thu, 05 Nov 2015 09:39:47 GMT

I tried with below changes, but results are not correct. Whats wrong with this ? When I replaced if condition with set analysis.

aggr(sum({$<Year=, Month=,user_txn_date={">=$(v_m12_date2) <=$(vSelectedMonthDatePrevious) "}>} ((bill_amount))),user_id)

Re: Aggr() function - Usage of CPU Cores

marcus_sommer — Thu, 05 Nov 2015 10:36:02 GMT

The reason could be that your variables returned (beside their values) another format and by set analysis must be also the format identically. For example would be a check on user_txn_date from 11/05/2015 = 42313 not match within set analysis but within an if-loop it does. If this happens you need an additionally date-formatting like date(42313, 'MM/DD'YYYY').

Further if the variable contained a string you would need single-quotes around it to access the variable properly - this meant if the variable contained this value: 11/05/2015 you would need: '$(var)'.

To check what you get I suggest you used a further chart with user_id as dimension and used those parts from the calculated dimension as a normal expression but used no labels for them. Within the label you could now see how qv interpreted the expression and could then adjust it.

sum(aggr(sum({<Year=,Month=, user_txn_date = {">=v_m12_date2<=vSelectedMonthDatePrevious"}>} bill_amount),user_id)) // new version

sum(aggr(sum({<Year=,Month=>}if (user_txn_date>=(v_m12_date2) AND user_txn_date<= (vSelectedMonthDatePrevious),bill_amount)),user_id)) // your original version

- Marcus