Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

stefan_fischer
New Contributor II

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

12 Replies
Not applicable

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

stefan_fischer
New Contributor II

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

Not applicable

Re: Sum values and counting rows with a macro

Stefan,

please share sample with data model.

regards

Darek

stefan_fischer
New Contributor II

Re: Sum values and counting rows with a macro

qvw attached

Not applicable

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

stefan_fischer
New Contributor II

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

Not applicable

Re: Sum values and counting rows with a macro

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

stefan_fischer
New Contributor II

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

Not applicable

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, 

- 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

Community Browser