topic Computations, Scripting and Expressions in QlikView

Computations, Scripting and Expressions

Fri, 23 Nov 2012 15:24:26 GMT

Hi,

I have 2 sets of question and I would like to know your idea.

1. I have 2 pivot table, the 1st one was getting the sum of all sales.

ex:

Desc	Sales
Sample1	200
Sample2	300
Sample3	400
Total	900

(this is just a sample, imagine it as a pivot table, my other dimension is month)

My second table compose of the percentage of the sales, it will depend on the total of the 1st table.

formula: sample1-> sales/total = percentage

Desc	Sales
Sample1	22.2
Sample2	33.3
Sample3	44.4
Total	100%

How can I produce this percentage if it is not joined to the 1st table?

2.) I have a date field and a list of customers and their item quantity.

Month	January 2012	February 2012	March 2012
Customer Name	Qty	Qty	Qty
CustNo	5	0	0
CustNo2	12	15	100
CustNo3	90	20	0
CustNo4	22	30	23
CustNo5	0	0	0

I need to filter this data who doesn't have a quantity for the last n months.

eg: Filter all the Customers who doesn't have any quantity for the last 2 Months [Date selected is February 2012(Included)]

output: CustNo5, since CustNo5 doesn't have any quantity from February2012~January2012

Note: I only have one Date Field.

I also want to know if it is better if I do it on Scripting or in the Chart

Thanks in advance!

-Donna

Re: Computations, Scripting and Expressions

chematos — Fri, 23 Nov 2012 16:38:38 GMT

I don´t understand the point 1 ... why do you need to separate the information in two tables?? Don´t they have the same dimensiones? You can use the option "Relative" in the options of the expressions sheet to get the percentages, else you need an expression using the same formula that table1 but adding sum(total Whatever) as denominator.

Question 2: I would do it on scripting if the rule is always for the last 2 months, if the rule depends on the selection of the user, it should be done in the chart

hope this helps.

Regards

Re: Computations, Scripting and Expressions

fosuzuki — Fri, 23 Nov 2012 17:31:02 GMT

Hi Donna,

I'm attaching a sample qvw I've created to check your item 2 requirement using set analysis. You can set the number of months for the calculation.

About item 2, I agree with Jose. Especially in cases when your calculation is complex, it is preferable to leave the heavy calculation "in the background" during the reload, so that the user experience is good when refreshing the charts.

Hope it helps you.

Fernando

Re: Computations, Scripting and Expressions

Sun, 25 Nov 2012 10:19:27 GMT

Alright, Thanks. I will try this one