Calculation of an Account Balance?

[Anonymous]

LOAD * INLINE [

    Acct, Balance, SalesPerson

    1212, 100, David

    1212, 100, Mike

    1212, 100, Lucy

    1525, 325, Jack

    1525, 325, Bill

    1525, 325, Edgar

    1630, 100, Paul

];

// In the preceding, I want only the Balance in the FIRST line of each Acct

// to be used in calculating a ClosingBal for each Acct.  I.E., for Acct 1212, the Balance is 100

//The calculation I need is the (Acct Balance) - count(SalesPerson) = ClosingBal where ClosingBal

//is the starting Balance for the next row in the Acct.

//The Report would look like the following:

Acct  Balance  SalesPerson ClosingBal

1212   100     David        99

1212    99     Mike         98

1212    98     Lucy         97

1525   325     Jack        324

1525   324     Bill        323

1525   323     Edgar       322

1630   100     Paul         99

// I only have QlikView Personal so I cannot process a 3rd party .qvw solution.  Anybody kind

// enough to help me would have to print out the solution.  I have spent two days on this with

// all kinds of approaches but still not got it.   Thanks  David

[campbellr]

Hi David

I found this when looking at calendars: Better Calendar Scripts | Qlikview Cookbook

It is an update on the calendar build that is standard with qlik training. makes sense.

It has the advantage of linking to a field name only and not a specific table to get the date range.

I make a field I call KeyDate and I have the calendar generate that as a field, as opposed to order date. I then load the date field in my data I want to filter on as KeyDate. I work with data with a number of date fields so choose just one.

Something to keep in mind is the calendar can only be used on one date field in an application. I have heard of ways around this but have not internalised how they work so for me one calendar belongs to one date field.

As for phone call / team viewer. I'm in Australia so time zone is a consideration. Team viewer is fine, also skype works too. My time zone is UTC +10. Can chat after hours my time. I'm happy to explain my workings.

[Anonymous]

Hi Ron:

Did not know about the Calendar date restriction.  Found my problem though.  The source data on the problem date field was coming in as YYYY/MM/DD with the actual '/' contained in the data fields.  This is different from every other time that I have tried to use the technique shown in the video.  Was really frustrating that the selected 'from' and 'to' dates were not showing up in the Selections Box and I had thought that somehow with my inexperience with Triggers that I had somehow compromised the Document-Wide properties.  I would like to give the Team Viewer approach a try sometime and noting the Time Zone difference between us, it is incumbent for me to play the part of the "night owl".  Perhaps over a weekend sometime and when you clearly have nothing better to do! 

Cheers

Dave

[campbellr]

The joys of imported data that looks like a date but is actually just text. I have found, by a process of frequent frustration, that when importing data it is beneficial to use num() to force numbers to be numbers, text() to force text to stay as text and date() to make dates behave as dates. I've had many issues bringing in data that uses text fields as identifiers that often contain only numeric data. mostly not a problem but if the first character is a 0 it will be lost. However it seems not always does it get lost. Also recently found that this text field contained a number with a decimal point. it was interpreted as a number and a trailing 0 was lost. It's a pain having to specify each field data type but in the long run it saves a lot of time.

Happy to have a team viewer session, maybe a Friday evening for me to fit with a Friday morning for you.

[Anonymous]

HI Ron:

As valuable a resource as you are, I would like to try a TeamViewer connection with you next Friday, May 11th.  With your time at 9:30 PM, my time is 7:30 AM.  If you are available, I would like to give it a go.  My email address is

david.balmer@sympatico.ca

If you could send me your email address, I can give you the TeamViewer ID and password.  We both have to have the same version of TeamViewer and I am currently on ver 13.  Perhaps we could just go over a few of the functions you provided in the 'balance-forward' solution.  I am not sure if Team Viewer allows us to speak over the connection but that would be something to try.

Best regards

David