Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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()))
My issue is more subtracting 201511 from 201601 for example
Sample attached
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()))
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