Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
solverproblems
Contributor
Contributor

How to transform number to date in Qlik Sense expression

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.

Labels (3)
1 Solution

Accepted Solutions
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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)

 

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

2 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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)

 

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
solverproblems
Contributor
Contributor
Author

Thanks!