How to transform number to date in Qlik Sense expression

[solverproblems]

In a graph I'm trying to show mounts between last month and this month with this expression:

=If( [SH_historico_1.ANOMES] >= '$(=Max([SH_historico_1.ANOMES])-1)' and [SH_historico_1.ANOMES] <= '$(=Max([SH_historico_1.ANOMES]))', [SH_historico_1.ANOMES])

When I write `[SH_historico_1.ANOMES]` I'm referring to Period, Like 'YYYYMM'
And when I subtract `-1` I'm just substact 1 to, example: 202002 -1 = 202001, so works, because It will calculate the correct period.

But... If the month it is january? YYYY01? example 202001 -1 = `202000` 00 month number it does not exist. The 00 it should be 12.

So I'm wondering what if I treat the number as a date?
I'm trying to use `Date()` function but I'm little stuck.

How it could be with correct syntax?

Another solution I'm thinking it is to set some code in editor code, but still developing the idea.

Any help it is welcome.

[kaushiknsolanki · Accepted solution]

Hi,

First of all you should create a date in script, like below.

Load *,

 Makedate(Left([SH_historico_1.ANOMES],4),Right([SH_historico_1.ANOMES],2)) as Date

From xyz;

Now you should update your expression as below.

If( Date>= '$(=Addmonths(Max(Date),-1))' and Date <= '$(=Max(Date))', Date)

[solverproblems]

Thanks!