topic Re: VB QlikView - Sum Function in QlikView

VB QlikView - Sum Function

Thu, 25 Feb 2016 15:01:03 GMT

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

Re: VB QlikView - Sum Function

marcus_sommer — Thu, 25 Feb 2016 16:32:49 GMT

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

- Marcus

Re: VB QlikView - Sum Function

Fri, 26 Feb 2016 08:20:22 GMT

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

Re: VB QlikView - Sum Function

Fri, 26 Feb 2016 08:42:00 GMT

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)

msgbox(dSum)

...

Re: VB QlikView - Sum Function

marcus_sommer — Fri, 26 Feb 2016 10:47:21 GMT

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

Re: VB QlikView - Sum Function

Mon, 29 Feb 2016 11:15:30 GMT

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

Re: VB QlikView - Sum Function

petter — Mon, 29 Feb 2016 21:20:53 GMT

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

Re: VB QlikView - Sum Function

swuehl — Mon, 29 Feb 2016 22:55:27 GMT

Try something like

Sub Test

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

End Sub

Re: VB QlikView - Sum Function

MarcoWedel — Mon, 29 Feb 2016 23:21:19 GMT

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

Re: VB QlikView - Sum Function

johnw — Mon, 29 Feb 2016 23:51:01 GMT

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.

Re: VB QlikView - Sum Function

Tue, 01 Mar 2016 08:23:20 GMT

Thank you, for all your response.

Here is the Sample file. In real Data source I have about 100 columns, should I create 2 table to keep those years separately for Xtable? and most important hot to properly create it? I saw few post about this, but still don't completely understand it.

Thank you,

Andrzej

Re: VB QlikView - Sum Function

Tue, 01 Mar 2016 11:32:31 GMT

Thank you all one more time. CrossTable does the work. Need to redesign some of the field I had in the Report it self, but this is the simple issue now.

Thank you.