
Re: Sum values and counting rows with a macro
Dariusz Mielczarek Apr 14, 2014 4:35 AM (in response to Stefan Fischer)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
Stefan Fischer Apr 14, 2014 5:04 AM (in response to Dariusz Mielczarek)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

Makro1.JPG 37.8 K

Re: Sum values and counting rows with a macro
Dariusz Mielczarek Apr 14, 2014 5:09 AM (in response to Stefan Fischer)Stefan,
please share sample with data model.
regards
Darek

Re: Sum values and counting rows with a macro
Stefan Fischer Apr 14, 2014 5:11 AM (in response to Dariusz Mielczarek)qvw attached

Makro.qvw 158.2 K

Re: Sum values and counting rows with a macro
Dariusz Mielczarek Apr 14, 2014 7:25 AM (in response to Stefan Fischer)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
Stefan Fischer Apr 14, 2014 11:16 AM (in response to Dariusz Mielczarek)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

Makro3.JPG 59.8 K

Re: Sum values and counting rows with a macro
Dariusz Mielczarek Apr 14, 2014 3:16 PM (in response to Stefan Fischer)Stefan,
your charts looks nice
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?
About your "avg" problem  where is it? In load script or in the chart expression?
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
Stefan Fischer Apr 15, 2014 3:17 AM (in response to Dariusz Mielczarek)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 SQLDatabase.
I’m from Germany.
Regards
Stefan
Makro.qvw 169.5 K

Re: Sum values and counting rows with a macro
Dariusz Mielczarek Apr 15, 2014 3:39 AM (in response to Stefan Fischer)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,
 calculate in load script
 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
Stefan Fischer Apr 16, 2014 3:07 AM (in response to Dariusz Mielczarek)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 SQLDatabase. I’ve testet the formula with different examples, and all resulats are ready. My qvw had a mistake and should be working.
Regards
Stefan
Makro.qvw 171.8 K

Re: Sum values and counting rows with a macro
Sergey Makushinsky Apr 16, 2014 3:20 AM (in response to Stefan Fischer)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
Stefan Fischer Apr 16, 2014 3:30 AM (in response to Sergey Makushinsky )
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










