Financial Period calculations

[#1]

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

[#2]

My issue is more subtracting 201511 from 201601 for example

Sample attached

[jonathandienst · Accepted solution]

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

[JoaquinLazaro]

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