topic Chart showing variances of Receivables value between two report dates in QlikView

Chart showing variances of Receivables value between two report dates

Sat, 25 Jun 2011 13:42:47 GMT

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

Chart showing variances of Receivables value between two report dates

stevedark — Mon, 04 Jul 2011 13:57:51 GMT

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

Chart showing variances of Receivables value between two report dates

Tue, 05 Jul 2011 17:38:40 GMT

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

Re: Chart showing variances of Receivables value between two report dates

Clever_Anjos — Tue, 05 Jul 2011 19:05:45 GMT

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.

Chart showing variances of Receivables value between two report dates

stevedark — Tue, 05 Jul 2011 21:54:56 GMT

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

Re: Chart showing variances of Receivables value between two report dates

Clever_Anjos — Wed, 06 Jul 2011 01:05:39 GMT

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.

Chart showing variances of Receivables value between two report dates

Wed, 06 Jul 2011 05:41:09 GMT

Hi Steve,

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

//Robert

Chart showing variances of Receivables value between two report dates

stevedark — Wed, 06 Jul 2011 06:43:31 GMT

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

Chart showing variances of Receivables value between two report dates

Wed, 06 Jul 2011 06:57:23 GMT

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

Re: Chart showing variances of Receivables value between two report dates

Clever_Anjos — Wed, 06 Jul 2011 07:03:45 GMT

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.

Re: Chart showing variances of Receivables value between two report dates

Clever_Anjos — Wed, 06 Jul 2011 07:03:45 GMT

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 3:57:31 AM.

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

Re: Chart showing variances of Receivables value between two report dates

Clever_Anjos — Wed, 06 Jul 2011 07:03:45 GMT

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 3:43:58 AM.

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

Re: Chart showing variances of Receivables value between two report dates

stevedark — Wed, 06 Jul 2011 20:53:01 GMT

Here is a working example of using actions on text boxes to modify dates.

Hope it is helpful.

Steve

Re: Chart showing variances of Receivables value between two report dates

Clever_Anjos — Thu, 07 Jul 2011 01:04:47 GMT

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 5:53:30 PM.

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

Re: Chart showing variances of Receivables value between two report dates

Thu, 07 Jul 2011 06:36:38 GMT

Hi Steve,

Thanks a lot for this example... I will go through it after work. Visually it does look better than the one I have now...

Chart showing variances of Receivables value between two report dates

Thu, 07 Jul 2011 14:24:18 GMT

Hi,

Thanks a lot. Looks good. I will check this out

Chart showing variances of Receivables value between two report dates

Thu, 14 Jul 2011 12:25:16 GMT

Hi Steve,

Very helpful file for me, thanks. I did not explore the "Set Variable" external action type in the text box...

One more question. The logic you showed will work perfectly if the DATE variable you want to impact does have the complete range of dates.

In my case, we do not load receivables every day, but 2, 3 times a week, so I miss some dates in my DATE variable(which shows the date of receivables information, equaling the date of transfering the data from transactional system to DB linked with QV). So what I need the "day button" to do, is to move me from currently selected date to the previous one present in DATE variable (tody is July 14, if last receivables were loaded on 10 July, then "day button" will show 10 July after one click). The month button would then move me to the date from last month closest to the one currently chosen when it comes to the day information (today is 14 July, reports last month around this date were loded at 10 Jun and 12 June and 18 June, so 12 June is chosen after clicking month button once)

Do you have any idea how this could be done?

//Robert

Chart showing variances of Receivables value between two report dates

stevedark — Thu, 14 Jul 2011 20:58:21 GMT

You will need to use a bit of Set Analysis to work out the next day in the direction you are headed on which there is a value. Assuming you have a field called DateField and your variables are as per your document for the previous day it will be something like:

=date(max({1<DateField={'<=$(=date(DATE1 -1, '$(vDateFormat)'))'}>}DateField), '$(vDateFormat)')

And the prior month will be:

=date(max({1<DateField={'<=$(=date(addmonths(DATE1, -1), '$(vDateFormat)'))'}>}DateField), '$(vDateFormat)')

Next day and next month should be relatively straight forward to work out.

I have seen an issue where QlikView doesn't like having the same variable twice in a single expression - but that seems to have gone in QV10 SR3.

Hope that helps,

Steve

Chart showing variances of Receivables value between two report dates

Fri, 22 Jul 2011 09:27:48 GMT

Hi Steve,

I edited your formulas a little bit and now they work exactly as I wanted them to work. Thanks for sharing the formulas with set analysis. I attach mine, in case someone finds them helpful:

DATE - dimension loaded from transactional DB, containing set of dates from the past with quite some missing

DATE_1 - my own variable changed by << , <, > , >> buttons

<< button formula

< button formula

>> button formula

> button formula

Re: Chart showing variances of Receivables value between two report dates

gschrepfer — Fri, 22 Jul 2011 14:01:54 GMT

Ich werde ab 15.07.2011 nicht im Büro sein. Ich kehre zurück am

08.08.2011.

mit freundlichen Grüßen

Gerhard Schrepfer