topic Re: Financial Period calculations in QlikView

Financial Period calculations

Wed, 13 Apr 2016 13:00:18 GMT

I have a table which I am importing with data linked to Financial period

Example

Account LastReconPeriod ReconType

1 201511 Full

2 201511 Part

The Current Financial Period is say 201601

If Recon Type is Full the ReconPeriod remains the LastReconPeriod

If Recon Type is Part the ReconPeriod is the LastReconPeriod -1 20 it is 201510

I need to give them a column with colour Green in the LastReconPeriod is the Current Period - 1, Orange if it is CurrentPeriod - 2, red if its CurrentPeriod -3 or more so for Account 1 the column is Orange, but Account 2 it would be red as the actual value is 201510 as it is only Partly Reconned

Any suggestions

Re: Financial Period calculations

Wed, 13 Apr 2016 13:02:10 GMT

My issue is more subtracting 201511 from 201601 for example

Sample attached

Re: Financial Period calculations

jonathandienst — Wed, 13 Apr 2016 13:35:42 GMT

If you convert the periods into date values or a numeric month sequence number, then the calculations are much easier. So during the initial load, load them something like:

LOAD ....

(Left(LastReconPeriod,4)*12) + Num(Right(LastReconPeriod,2)) as LastPeriodSequence,

If the current period is defined in a similar way (lets call the value CurrentPeriodSequence), then the colour expression becomes simply:

=If(CurrentPeriodSequence - LastPeriodSequence - If(ReconType = 'Full', 0, 1) = 1, Green(),

If(CurrentPeriodSequence - LastPeriodSequence - If(ReconType = 'Full', 0, 1) = 2, Orange(), Red()))

Re: Financial Period calculations

Joaquin_Lazaro — Wed, 13 Apr 2016 14:26:01 GMT

Here you have a link Canonical Date to a blog above dealing with dates.

If you want customize some cell formats this What is possible with calculated expressions in... | Qlik Community explains some tricks

Joaquín