Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
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
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:

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

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
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:

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
joaquinlr
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

Community Browser