Chart showing variances of Receivables value between two report dates

[#1]

Hi,

I would appreciate your help in the following issue.

I have a database showing open receivables for quite a number of dimensions, report date is one of them. I would like to create a variance chart showing me the open receivables balances for two chosen report dates and the variance for a created group of dimensions between those balances. Something like attached excel (I put there variance only for customers dimension, but the idea is there - I change Date1 or Date2, my chart shows the variance between the Dates selected).

WHAT I KNOW:

To have such a chart I need to have 3 dimensions in Chart Properties/Expressions: The one defining DATE1, the one defining VARIANCE and the one defining DATE2. In the VARIANCE dimension I need to define "Bar Offset" property so that I could get correct bars.

WHAT I DO NOT KNOW:

Majour unknown is a proper definition of DATE1, DATE2 dimensions - as I mentioned one of my DB dimensions is "Report date". I would like the DATE1, DATE2 were defined in the following way: If user chooses range of dates from "Report date", then the minimum date of such range is DATE1 (so DATE 1 shows receivables balance for minimum date chosen by user in its range), the maximum is DATE2. If user changes only one value from "Report date" then 31 Dec of the previous year to the one chosen is DATE 1, the selection is DATE2. If used does not make any solection in "Report date", then chart is not displayed (could be some message instead, but this is more a cosmetics)

VARIANCE - how to correctly define this dimension to show variances for created by me cyclical group call it REC_VAR that I want to place as dimension of this chart.

I would appreciate any help regarding this issue. I have not done such advanced things for some time, so my know-how in such matters is based on imitating the similar examples to the problems I do face.

Count on you...

Robert

[stevedark]

Hi Robert,

You do not need the two dates as dimensions, in fact you only need the one field as dimension (a cycle group for this purpose can be a good thing).  It is in the expressions that the matching on the date is done, and a set of syntax called Set Analysis needs to be used.

You will need a way of selecting the two dates, ideally putting them into variables.  Two sliders could be used, or a couple of tables as data islands with 'always one selected value' picked.  I tend to default variables in the load script and then put buttons in with actions to change the dates.

The expressions will then read a bit like this:

sum({<Date={'$(vFirstDate)'},Year=,Month=>}Receivables)

sum({<Date={'$(vLastDate)'},Year=,Month=>}Receivables)

sum({<Date={'$(vFirstDate)'},Year=,Month=>}Receivables) /

sum({<Date={'$(vLastDate)'},Year=,Month=>}Receivables)

The syntax in the squiggly brackets basically says match date to the variable, but ignore any selections on Year and Month (you will probably have other date based fields to ignore?).

One thing that always causes a bit of a headache with this syntax is that the format for the date variable must be exactly the same as the format of the Date field.  Suggest using the Date function in the load script and on setting the variable to ensure this.  I can post you an example document if required - but you should be able to find all you need to acheive this in other QlikCommunity posts.

Hope that helps.

- Steve

[#3]

Hi Steve,

Thanks a lot for your answere. I managed in kind of simplified way, meaning I created 2 input boxes - DATE1 and DATE2.

In the variance chart I have the following formulas:

For 1st column:

=if(RowNo() = 1, sum({$< DATE = {"$(DATE1)"}>} total RECEIVABLES

) )

For the variance (cyclical group):

(sum({$< DATE = {"$(DATE2)"}>} RECEIVABLES )

-sum({$< DATE = {"$(DATE1)"}>} RECEIVABLES ) )

For the last column:

=if(RowNo() = NoOfRows(),

sum({$< DATE = {"$(DATE2)"}>} total RECEIVABLES )

This works fine, however choosing the dates in input boxes could be done in different way.

Could you attach any example of defining the date variables in the load script? This sounded interesting

Thanks a lot once more

Robert

[Clever_Anjos]

I am out of the office until 07/07/2011.

Retorno na 5a feira a noite.

Se for urgente, por favor me contacte no celular.

Note: This is an automated response to your message "[QlikView Developers

Group] - Re: Chart showing variances of Receivables value between two

report dates" sent on 5/7/11 2:38:51 PM.

This is the only notification you will receive while this person is away.

[stevedark]

Hi Robert,

To default the variables in the load script you simply need a couple of lines of code like this:

let DATE1 = date(today(), 'DD/MM/YYYY');

let DATE2 = date(addmonths(today(), -1), 'DD/MM/YYYY');

What I would add to having the input boxes (or perhaps replace the input boxes with text boxes) is buttons to change the dates.  I would go for text boxes with legends like this:

<<    <   >   >>

With the single chevrons moving forward or back a day, and the double ones moving a month at a time.  You would just need to create four text boxes and add Actions of type External \ Set Variable and set DATE1 to each of the following:

=date(addmonths(date#(DATE1, 'DD/MM/YYYY'), -1), 'DD/MM/YYYY')

=date(date#(DATE1, 'DD/MM/YYYY') - 1, 'DD/MM/YYYY')

=date(date#(DATE1, 'DD/MM/YYYY') + 1, 'DD/MM/YYYY')

=date(addmonths(date#(DATE1, 'DD/MM/YYYY'), 1), 'DD/MM/YYYY')

The difference between Date and Date# is critical, look up in the help what both of these do if you you are not sure.  You will need to use the right date format string to match the format of the DATE field.

Let me know how you get on.

- Steve

[Clever_Anjos]

I am out of the office until 07/07/2011.

Retorno na 5a feira a noite.

Se for urgente, por favor me contacte no celular.

Note: This is an automated response to your message "[QlikView Developers

Group] - Re: Chart showing variances of Receivables value between two

report dates" sent on 5/7/11 6:55:08 PM.

This is the only notification you will receive while this person is away.

[#7]

Hi Steve,

I think Your example of text boxes with legends was not attachedto your coments

//Robert

[stevedark]

Hi Robert - no attachment, I was just trying to show how you could create simple buttons using text boxes with greater than and less than symbols, eg.:

If you are unable to get the actions behind the text box functioning correctly then I can put together an example and upload.

- Steve

[#9]

Hi,

I would be grateful for such example or indication where I can find one. To be honest being a controller, and not BI tools developer it is much more simple for me to adjust example to my case, than to follow general indication and apply it to my report ..

Thanks a lot in advance

Robert

[Clever_Anjos]

I am out of the office until 07/07/2011.

Retorno na 5a feira a noite.

Se for urgente, por favor me contacte no celular.

Note: This is an automated response to your message "[QlikView Developers

Group] - Re: Chart showing variances of Receivables value between two

report dates" sent on 6/7/11 2:41:26 AM.

This is the only notification you will receive while this person is away.