topic Calculated members in QlikView

Calculated members

Tue, 19 Oct 2010 16:42:22 GMT

Is it possible to create calculated member in QV. For example in Account Dim I have Energy and Gas, and I Want to display Energy + Gas in the chart. Something like this:

Account	Amount	AmountD	Ratio	Amount+AmountD
Energy	1,370	134	10.22	1504
Water	2,104	134	15.70	2238
Gas	2,131	134	15.90	2265
Energy + Water	3,474	268	25.93	3742

Calculated members

Tue, 19 Oct 2010 17:00:59 GMT

You can make that on your script through call a dimension same name!

Something like :

IF(Account='Energy' OR Account='Gas', 'Energy+Gas') AS Account

Calculated members

Tue, 19 Oct 2010 18:22:41 GMT

Ok, but would it be possible in the chart, with formulas? I ask because other BI tools have this feature.

Calculated members

Tue, 19 Oct 2010 18:49:33 GMT

I don´t know it is possible....you can try add a calculated dimension..

Calculated members

johnw — Tue, 19 Oct 2010 19:22:30 GMT

Do this:

Groups:
LOAD * INLINE [
Account Group, Account
Energy, Energy
Water, Water
Gas, Gas
Energy + Water, Energy
Energy + Water, Water
];

Then use Account Group instead of Account as your dimension. Now yes, I could probably accomplish the same thing with some calculated dimensions and interesting expressions. I wouldn't. I would do it with data as shown above.

Hmmm, just noticed that you want 268 for the AmountD column. But are you sure you want 268? I'd think you'd want 134 to match the 25.93 ratio, since 3474/134 = 25.93. And I don't think the Amount + AmountD column makes any sense, or at least I don't understand it. Anyway, I'd still probably add the account groups, and just fix any formulas that need fixing, but I'm not sure any of them need fixing.

Calculated members

pover — Tue, 19 Oct 2010 19:22:35 GMT

It sometimes is better to make 2 tables and suppress the header of the second table to make it look like they are the same table, but if it has to be in the same table, you can use the valuelist() function to create any grouping. It can sometimes be a little cumbersome to work with (commenting the expression helps), but with that function you can basically put whatever formula in whatever cell you want as if it were Excel.

Regards.

Edit: It is better to change the script as recommended above instead of the attached solution for performance reasons, but sometimes it is good to give some kind of flexibility to the power user on the graphic side. Especially those ubiquitious Excel users.

Calculated members

johnw — Tue, 19 Oct 2010 19:41:59 GMT

I agree that the valuelist() + pick(match()) approach is a valuable one. I wish there were a simpler way to get that sort of cell-by-cell flexibility, but I haven't thought of one.

Calculated members

Wed, 19 Jan 2011 00:39:41 GMT

Pedro,

It's real easy. Use Set Analysis:

Example:

sum( {$<[Account] = {Energy} >} Value ) + sum( {$<[Account] = {Water} >} Value )

That's all!

Regards,

Bas

Calculated members

johnw — Wed, 19 Jan 2011 00:57:21 GMT

Set analysis can easily add energy to water, yes, though the expression could be simplified (as used by Karl in his solution):

sum({<Account={'Energy','Water'}>} Amount)

But MERELY using set analysis to restrict the accounts to energy and water has the effect of simply removing the "Gas" row from the chart. That's not what we wanted. We wanted to add an "Energy + Water" row to the existing chart. Good try, though. Welcome to the forum.