Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

VB QlikView - Sum Function

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

Tags (1)
1 Solution

Accepted Solutions

Re: VB QlikView - Sum Function

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

11 Replies

Re: VB QlikView - Sum Function

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

- Marcus

Not applicable

Re: VB QlikView - Sum Function

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...

Highlighted
Not applicable

Re: VB QlikView - Sum Function

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)

...

Re: VB QlikView - Sum Function

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

Not applicable

Re: VB QlikView - Sum Function

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...

MVP
MVP

Re: VB QlikView - Sum Function

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

MVP
MVP

Re: VB QlikView - Sum Function

Try something like

Sub Test

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

End Sub

Re: VB QlikView - Sum Function

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

MVP
MVP

Re: VB QlikView - Sum Function

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.

Community Browser