topic Re: Best practice in QlikView

Best practice

Sun, 24 Mar 2013 13:43:22 GMT

Hello,

I've a formula sum(QTY*Price - QTY*Cost). Which are the Best practice rules for write this better ?

Is it possible by the following formula :

sum(QTY(Price-Cost)) or sum(QTY*Price) - sum(QTY*Cost)

Who can give me the best solution, maybe there is another one.

Thanks in advance.

Best Regards,

Else

Re: Best practice

maksim_senin — Sun, 24 Mar 2013 23:03:56 GMT

Hi Else,

It's better to estimate your data model before saying whether some expression is correct, then it will be possible to say whether it corresponds to "best practice" (b.p.).

I assume you have a table of (e.g.) sales with "Good" dimension and values of "Qty", "Price", "Cost". If you need to define a profit the correct expression would be Qty*Price - Qty*Cost or Qty*(Price-Cost). At least such expression would work in Excel. In QlikView of course you should use the expression with Sum() aggregation function in order to be able to have a profit in any dimension(s), if you have some others except "Good".

As for the expression Sum(Qty*Price)-Sum(Qty*Cost) - what its purpose is?

As for b.p., I'm not sure the b.p. covers this case, it states that expressions shall be as short and simple as possible, i.e. in this case it's better, for instance, to calculate saleRevenue = Qty*Price and saleCost = Qty*Cost for each sale in load script, so your expression will look like Sum(saleRevenue-saleCost), or you can calculate saleProfit = Qty*Price - Qty*Cost in the load script, and your expression will be Sum(saleProfit).

At least that is my understanding of b.p., as for writing of expressions - it's important to understand their purpose.

Best regards,

Maxim

Re: Best practice

Mon, 25 Mar 2013 06:53:56 GMT

Hello Maxim,

Thanks for your answer.

It's a colleague of me who asked the question. He isn't a developer, so I think he will do it in the expression-tab of the Bar Chart.

Best Regards,

Else

Re: Best practice

jagan — Mon, 25 Mar 2013 07:11:01 GMT

Hi,

As per Maxim Senin suggestion it is better to calculate Salesprice, CostPrice and profit in the script itself, so that there is no need to calculate this during runtime, this improves performance of the charts. Alos the expressions would be simpler like

=Sum(Profit)

Hope this helps you.

Regards,

Jagan.