topic Lock variable value in QlikView

Lock variable value

Fri, 15 May 2015 14:00:34 GMT

Hi guys,

I am busy building a dashboard where I am trying to lock the value of a variable. I have tried everything, from the {1} to doing the "variable=" in my set analysis.

The thing is, I don't want to have to list all of the possible filters that might affect my calculation and the {1} does not seem to lock the value when I click on a filter.

The variable that I have created looks as follows:

=(vTotalCost*vInsurance100)/count({1}{<Product= {'Insurance'}, txn-= {''}>} txn)

Basically, an individual must choose how much they feel that Insurance contributes to the total cost. Let's say the total cost is 10, then insurance contributes 6 to that, so 60%.

Then I want to count the number of transactional lines where the product is Insurance, so that I can calculate a cost per insurance txn.

I want to fix that value, like in Excel where you put the $ signs around a cell value.

Then I want to be able to call this value in my Qlikview at any time and be able to for example, calculate the number of txns that Males have on the Insurance product and understand that if the average txn cost for Insurance is 3, and Males have 10 Insurance transactions, then the total cost of Male contributions to Insurance is 30..

I want this to be fixed so that it does not matter in which way I slice or dice my chart, it will always use the precalculated value of the cost per txn and I can apply it across the board.

Thank you for your help - I have googled and have struggled to find a solution...

Re: Lock variable value

Gysbert_Wassenaar — Fri, 15 May 2015 15:33:31 GMT

What are vTotalCost and vInsurance100? Those look like variables too. Do these variables also contain expressions that can be influenced by selections?

Re: Lock variable value

Sun, 17 May 2015 08:46:31 GMT

Hi Gysbert,

Thank you for responding - no, these are just input variables:

vTotalCost can be 1000 for example and vInsurance can be 65. These are sliders...

Oops, the equation should be:

=(vTotalCost*vInsurance/100)/count({1}{<Product= {'Insurance'}, txn-= {''}>} txn)

Re: Lock variable value

Peter_Cammaert — Sun, 17 May 2015 08:53:25 GMT

I'm not really sure about the expression syntax you are using. Does it work if you put the entire set 1 inside the second one? Like:

=(vTotalCost*vInsurance/100)/count({1<Product={'Insurance'}, txn-={''}>} txn)

Re: Lock variable value

Peter_Cammaert — Sun, 17 May 2015 08:59:01 GMT

Another idea: why count the number of transactions over and over again if this value remains constant? You could calculate a variable say vNoOfInsuranceTransactions in your Load script and use that one in expressions like:

=(vTotalCost * vInsurance) / (100 * vNoOfInsuranceTransactions)

Re: Lock variable value

Sun, 17 May 2015 09:20:07 GMT

I will only be able to test this tomorrow, as the model is on the server at work..

I don't mind doing that, that will make the model faster, but my concern is if that will lock down the value of the cost per insurance transaction that I am calculating above.

The problem that I experience, is that let's say I have 5 products, Insurance, Wealth, Health, Short Term and Risk, and each of these take 20% of the total cost, so $200. Now, I want to segment the above mentioned calculated costs by Income Brackets, when I filter on income brackets, the total cost gets allocated to the income bracket that I have filtered to... So I want to fix the cost per transaction to be constant after the sliders have been applied... and obviously the sliders needs to be able to change according to the user's discretion..

My data is currently only in one flat table.

I hope I made sense

Re: Lock variable value

Peter_Cammaert — Sun, 17 May 2015 09:28:58 GMT

That's why in my example variable vNoOfInsuranceTransactions only contains a precalculated count, not the entire expression. Using set {1} means that the count is immune to all selections except the ones you supply in the set modifier. That count can be translated 1:1 into a LOAD COUNT() WHERE TX='Insurance'; script statement.

You see I adhere to an old motto: "Everything that can be done in the script, should be done in the script"

Peter

Re: Lock variable value

Tue, 19 May 2015 11:41:24 GMT

Thank you Peter - this worked we also changed the set analysis to a count if statement and this made our data "sliceable"