VB QlikView - Sum Function

[#1]

Hi guys,

I am trying to use VB in the QlikView. I have variable with field name, so as part of the code I want to sum all the values in this field.

This should be so simple (at least it is in VBA Excel), but I spent an hour on it and can't make it works...

Any Ideas?

Thank you.

Andrzej

[marcus_sommer]

What's your aim? I would rather not try to aggregate qlikview fields with vbs macros.

- Marcus

[#3]

This will be the part of the bigger script (I hope). As QlikView is a data visualization tool with the good support for the analyses, I would expect that VB would have the simple sum function...

[#4]

I think I cracked it. Basically I couldn't find sum function, so I had to simulate it:

...

dim dSum

dim vYear

set vMinYear=ActiveDocument.Variables("vMinYear")

vYear=vMinYear.GetContent.String

  set val=ActiveDocument.Fields(vYear).GetPossibleValues(1000)

  for i=0 to val.Count-1

    dSum = dSum + cDbl(val.Item(i).Text)

  next

msgbox(dSum)

...

[marcus_sommer]

Nope, it hasn't - it's a complete different layer. But again what's the aim? I have the feeling that you runs in a wrong direction ...

- Marcus

[#6]

Marcus Sommer <span class="icon-status-icon icon-mvp" title="Mvp"></span> wrote:

Nope, it hasn't - it's a complete different layer. But again what's the aim? I have the feeling that you runs in a wrong direction ...

- Marcus

Ok... I have columns that named by years (2014, 2015, 2016 etc). I have slider where I can select years I want to have report for. So I need to sum all columns that are selected in slider. I found solution how to make this sum, but by some reason the ".GetPossibleValues(1000)" doesn't come up with the same number of possible items as the "GetPossibleValues" Function in the report. Trying to figure out what is wrong...

[petter]

This s highly inefficient - QlikView will do that with a single expression several orders of magnitude faster...

[swuehl]

Try something like

Sub Test

MsgBox(ActiveDocument.Evaluate("Sum($(vField))"))

End Sub

[MarcoWedel · Accepted solution]

don't go in that direction.

Forget about VB at all in this case.

Instead post a small sample application and your expected result in case of some selections.

Maybe you can use a crosstable load to convert your multiple year columns into one year field and use this one to perform your selections.

There surely are many possibilities to meet your requirements, none of them needing VB scripts.

hope this helps

regards

Marco

[johnw]

I agree with Marco. I would use a crosstable load or some other approach to make a Year field. Then select the years like any other field, and sum like any other sum. Where at all practical, I prefer my complexity be in my load script, not in the front end, and definitely not in macros.