Help required in one Expression

[shiveshsingh]

Hello Guys

I need one help in below expression - 

(SUM({$<OrderDate={">=$(=DATE(MonthStart(MAX({$}OrderDate)),'DD/MM/YYYY'))<=$(=Date(MAX(OrderDate),'DD/MM/YYYY'))"}, FiscalMonth, Date,EXT_NEW_FLAG = {'N'}>} ABC))

Dimension - Day

Issue  - When i use EXT_NEW_FLAG = {'N'} in set expression, i get only those days which have this value. I don't get the days where the expression yields null.

like - If expression value is not present for 15 jan and 16 jan, these two days will not be shown in x axis

But when i remove EXT_NEW_FLAG = {'N'} in set expression, i get all the days of the current month whether expression gives value or is null.

Any leads?

[Silambarasan1306]

My understanding is, Incase if the value is not available for a specific day, it should be shown as null. Don't uncheck the include zero values option in the add-ons option.

[JordyWegman]

Hi,

This is because the expression only is true for those days. When it's not true, it won't show the values. What happens if you just ad 1 as an expression? It should give you all the days again.

Jordy

Climber

[shiveshsingh]

Thanks, I've already tried that. Not working.

[shiveshsingh]

Let me give it a try.

[tresesco]

What do you wish to get as a result? This behavior is expected. If you want to still show the excluded values, try :

 - Check 'Show All Values' in dimension tab

- Uncheck 'Suppress Zero Values' in presentation tab.

- Check 'Suppress When Value is Null' in dimension tab

[shiveshsingh]

I am working in qliksense, have already done that but no luck.

[tresesco · Accepted solution]

Sorry, I missed that. You can try tweaking the expression like:

(SUM({$<OrderDate={">=$(=DATE(MonthStart(MAX({$}OrderDate)),'DD/MM/YYYY'))
<=$(=Date(MAX(OrderDate),'DD/MM/YYYY'))"}, FiscalMonth, Date>}
 If(EXT_NEW_FLAG = 'N', ABC))) 

[pradosh_thakur]

Try this please

(SUM({$<OrderDate={">=$(=DATE(MonthStart(MAX({$}OrderDate)),'DD/MM/YYYY'))<=$(=Date(MAX(OrderDate),'DD/MM/YYYY'))"}, FiscalMonth, Date,EXT_NEW_FLAG = {"=match(EXT_NEW_FLAG ,'N') or len(trim(EXT_NEW_FLAG )) =0"}>} ABC))

[shiveshsingh]

Hi @tresesco

Thanks, it worked.

Can you please explain ?