12 Replies Latest reply: Apr 16, 2014 3:30 AM by Stefan Fischer

# Sum values and counting rows with a macro

Hi,

I have a problem with the addition of values in a table. I guess it can be solved with a macro. Unfortunately I have no experience in with makros. It should calculate the first 12 values in an additional column and the number of rows should be counted. The rows with null values are not be counted.

Regards

Stefan

• ###### Re: Sum values and counting rows with a macro

Stefan,

I'm pretty sure that you do not need a macro.

I think, you need to prepare appriopriate data model and then use normal expressions with some conditions n your charts.

If you want some example, please explain in more detail what source data do you have and provide sketch of analytics you would like to prepare in QV.

regards

Darek

• ###### Re: Sum values and counting rows with a macro

Hi Darek,

thanks for your reply. There is a tricky problem. I've created a special formula in cal 52 week to calculated values. But if is a 0 value in first 12 row, i've get a wrong result.

Regards

Stefan

• ###### Re: Sum values and counting rows with a macro

Stefan,

please share sample with data model.

regards

Darek

• ###### Re: Sum values and counting rows with a macro

qvw attached

• ###### Re: Sum values and counting rows with a macro

Stefan,

i'am sorry, because maybe i'am not clear about your business needs. Anyway, when i see expression like this:

= If( RowNo(TOTAL)=1, rangeavg (below(total sum(Value),0,12)),

If( RowNo(TOTAL)<=2,

((below(total sum(Value),-1,1)

+ below(total sum(Value),0,1)

+ below(total sum(Value),1,1)

+ below(total sum(Value),2,1)

+ below(total sum(Value),3,1)

+ below(total sum(Value),4,1)

+ below(total sum(Value),5,1)

+ below(total sum(Value),6,1)

+ below(total sum(Value),7,1)

+ below(total sum(Value),8,1)

+ below(total sum(Value),9,1)

+ below(total sum(Value),10,1))/12),

(((above(total sum(Value),0) * vB0 + above(total sum(Value),1) * vB1 + above(total sum(Value),2) * vB2) -

(below(total Column(2),-1,1) * vA1) - (below(total Column(2),-2,1) * vA2)) / vA0)))

my first thought is that may be something wrong

I suggest you to start from the beggining. Let me know what is your source data and what result you need in application. You may need to adjust somehow the data model

regards

Darek

• ###### Re: Sum values and counting rows with a macro

Hi,

this calculation will be planning line chart. Pls look at pic.

First and second value is the average of 12 value in row SU. At row three last formula will be calculated.

Regards

Stefan

• ###### Re: Sum values and counting rows with a macro

Stefan,

I can see, that in your xls column 52 woche is calculated from some fields from columns B adn C and also some basel values.

I can see also, that in your QlikView sample those 52 weeks and 12 week values you load inline, so, i can expect, that you plan load them "from outside" or calculate it in your script. Anyway i feel, that you plan, that those fields will be calculated and ready to show just after data reload.

I dont  know your business case, but don't you need any selections and calculations "on the fly" in your application?

And last question.... where are you from? Maybe we can meet and take a look closer on your case ?

regards

Darek

• ###### Re: Sum values and counting rows with a macro

Hi Darek,

this function will damp the line in my chart. My problem is, if values have 0 or I get less as 12 rows, this formula will not work correct. I will load data from a SQL-Database.

I’m from Germany.

Regards
Stefan

• ###### Re: Sum values and counting rows with a macro

Stefan,

i understand, that Value column (in your QlikView table SU column) is data in week granularity. You getting it from external database.

1. I try to understand if 52 week and 12 week KPI's you need:

- get from externel database,

- or maybe calculate it on the fly (using chart expression).

I understand, that for each week you have some KPI (12 weeks) related to last (or maybe next) 12 weeks.

2. Is 52 weeks KPI also related to 52 last (or maybe next) weeks?

3. Do you need in your appliocation some another KPI's, like for example 24,40, etc. weeks, or maybe you like to let user set number of weeks to have dynamic X weeks KPI?

I'am not sure if it is difference you are talking about, but i can see in your table that for instance for week 2011/34 you have value 14331 in column "52 weeks" and value 13.829 in column "cal 52 weeks".

I'am in Warsaw, so i have 6 hours by car to Berlin

regards

Darek

• ###### Re: Sum values and counting rows with a macro

Hi Darek,

thanks for you interested of my problem. For calculation 12 values are required for first value. Values are booking entry of the day, but there are also days without values, but all weeks should be show in chart. There was also a problem with 0 values with QlikView, but this problem can be solved in SQL-Database. I’ve testet the formula with different examples, and all resulats are ready. My qvw had a mistake and should be working.

Regards
Stefan

• ###### Re: Sum values and counting rows with a macro

Hi Stefan,

Can you explain in simple words what you try to calculate?

Because, for example, you mention "all weeks should be show in chart" - this issue could be solved by additional table with calendar..

Probably you should start from the your requirements and small example..

• ###### Re: Sum values and counting rows with a macro

Hi Sergey,

i will calculate row 52 week with a differential equation. First and second value will be calculated with first 12 values from row SU. Third and follow valuves are calculated with a special formula.

Regards

Stefan