Numeric searches

[HelloHMC]

I want to use the method below to limit the range of number in chart expression, but it can‘t limit the range of number successfully. 

https://help.qlik.com/en-US/sense/May2024/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/SetAnalysi...

This is my test data:

[XXX]:

load 

*

inline [

A,B

1,2

2,3

3,4

4,6

5,6

4,6

3,7

];

Then I want to build a line chart, the chart expression is as below:

Dimensions: "A"

Measures: Count({$<A={">=3<= 4)"}>}B) 

But the line chart, no matter what is the end value of “A” I have chosen, it will display to 5.

That means the expression didn't refrain the "A" correctly.

I want to confirm if I write the chart expression correctly or not.

BTW,  I have written it according to the qlik learning.

[ysalvi43]

Hello 
Please find below working logic, attaching screenshot for reference 
Logic: Count({$<A={">=3 <=4"}>} B)

Reason: 

There is a ) inside the search string, which breaks the syntax

Screenshot:

If you find useful, Please mark this as Solution

Thanks

[HelloHMC]

Thanks ysalvi43, that is make sense.

But what I want to build is using a more complex chart expression which is as below:

count({$<DATE_WORK={">=$(=date(max(DATE_WORK)-30)) <= $(=max(DATE_WORK))"}, WORK_STATE={'Y'}>}[Employee ID])

This chart expression has the same problem of the ending limitation of date is not successful,

so how can I modify it?

thanks again,

BR!

Previe

[ysalvi43]

Hello @HelloHMC 

Please test this formula:
count( {$<
DATE_WORK={">=$(=max(DATE_WORK)-30)<=$(=max(DATE_WORK))"},
WORK_STATE={'Y'}
>} [Employee ID])

Tested at my end working fine with raw data:

Thanks 

[HelloHMC]

Thank you,

but it doesn't work at all~

May be because there are two forms of date, and that is why I write the chart expression like previously!

count( {$<DATE_WORK={">=$(=max(DATE_WORK)-30)<=$(=max(DATE_WORK))"},WORK_STATE={'Y'}>} [Employee ID]) /
count( {$<DATE_WORK={">=$(=max(DATE_WORK)-30)<=$(=max(DATE_WORK))"}>} [Employee ID])

BR!

[ysalvi43]

Hello 

You can use floor for those date, it will convert date formatting to number

Thanks  

[Daniel_Castella]

Hi @HelloHMC 

If the -30 means that you want to set the date for the previous month, I would recommend this formula:

count( {$<DATE_WORK={">=$(=addmonths(max(DATE_WORK),-1))<=$(=max(DATE_WORK))"},WORK_STATE={'Y'}>} [Employee ID]) /
count( {$<DATE_WORK={">=$(=addmonths(max(DATE_WORK),-1))<=$(=max(DATE_WORK))"}>} [Employee ID])

Kind Regards

Daniel

[Lech_Miszkiewicz]

Hi, 

For date searches with >= & <= you either have to format search string as date or create an equivalent field in numeric format. 

[HelloHMC]

I want to display the past 30 days data of the max date I have chosen.

So it is not just choose last month's date.

But also thank you.

Cheers~

[HelloHMC]

Yes I get you.

But my original chart expression is as below:

count( {$<DATE_WORK={">=$(=date(max(DATE_WORK))-30)<=$(=max(DATE_WORK))"},WORK_STATE={'Y'}>} [Employee ID]) /
count( {$<DATE_WORK={">=$(=date(max(DATE_WORK)-30))<=$(=max(DATE_WORK))"}>} [Employee ID])

This chart expression has the same format of start and end date, however it is also not work.

So what is the problem of my chart expression and how can I adjust it?