topic Re: Help for build a table in QlikView

Help for build a table

Wed, 26 Feb 2014 08:49:46 GMT

Hello,

i have a graph simple table with many expressions and i would like to do in another table the sum and the avg of the values that stand in the first table.

For example, i have a table like this (the columns are expressions):

And i want to get a second table like this:

How can i do this please?

Thanks,

Best Regards,

Loïc

Re: Help for build a table

sujeetsingh — Wed, 26 Feb 2014 08:56:50 GMT

Derieve a field as

-----MonthYear=Month(Date)&'/'& yera(Date)

Then use calculated dimension as : 'SUM'&' ' & MonthYear

Now add the expressions as Sum(1),Sum(2),sum(3),,,,,,etc

In the same way you can get the one with Avg

Re: Help for build a table

datanibbler — Wed, 26 Feb 2014 08:58:27 GMT

Hi loic,

afaIk, it is not possible to directly reference a formula in another table - you have to actually write the formula. Then you can put any aggregation fct like sum() or avg() around it.

=> You just have to keep in mind the dimension:

- when the dimension is the same in both tables, your avg() will be calculated over the same range of values,
you're ok.

<=> Alternatively, you can use the aggr() function to simulate a dimension ("aggregate by (e.g. month)")

HTH

Best regards,

DataNibbler

Re: Help for build a table

Wed, 26 Feb 2014 09:32:14 GMT

My colums 1, 2, ... are aleady exppressions with sum or avg.

But expressions like sum(sum(... or avg(sum(... don't work.

Re: Re: Help for build a table

Wed, 26 Feb 2014 10:01:48 GMT

Loïc,

You are not allowed to do sum(sum(... or nest twice an aggregation function : sum, avg, max ... You need to insert in the middle the aggr() function.

If there is a relation between dats and month (it should be), the expression with sum() should remain the same. Sales for ex. are completely additive. Sum(Sales), if your chart is not broken by days will sum up all the sales for the month.

So, as DataNibbler told you, you have to rewrite your expressions. For sum() it will be a simple copy/paste. For avg(), it will be more complicated or impossible: you need the aggr() function that does not accept a calculated dimension, and you will have a calculated dimension.

See the png attached to see what I have done:

A calculated dimension: Valuelist('SUM', 'AVG') to get two rows

Expression 1 : if it is the first row then sum, if not average

in QV language: if(rowno()= 1, sum(XXX), sum(XXX)/count(Distinct Gamme)) //Gamme is my Dimension and XXX my measure

For each expression, you need to know by what number you will have to divide.

Fabrice

Re: Help for build a table

sujeetsingh — Wed, 26 Feb 2014 10:42:00 GMT

just use that expression .

One thing you can do is that you can give me a sample with 9 or ten records by INLINE table load