topic Re: How to transform number to date in Qlik Sense expression in App Development

How to transform number to date in Qlik Sense expression

solverproblems — Sat, 16 Nov 2024 02:53:44 GMT

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.

Re: How to transform number to date in Qlik Sense expression

kaushiknsolanki — Tue, 24 Mar 2020 18:54:43 GMT

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)

Re: How to transform number to date in Qlik Sense expression

solverproblems — Tue, 24 Mar 2020 23:00:23 GMT

Thanks!