# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Rules, plus terms and conditions, can be found here.
Not applicable

## Financial Period calculations

Hi

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

Tags (1)
1 Solution

Accepted Solutions
MVP

## Re: Financial Period calculations

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:

(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()))

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
3 Replies
Not applicable

## Re: Financial Period calculations

My issue is more subtracting 201511 from 201601 for example

Sample attached

MVP

## Re: Financial Period calculations

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:

(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()))

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Valued Contributor II

## Re: Financial Period calculations

HI

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