Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Honored Contributor

Help required in one Expression

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?

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Help required in one Expression

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))) 

View solution in original post

11 Replies
Contributor III

Re: Help required in one Expression

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.
Highlighted
Partner
Partner

Re: Help required in one Expression

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

Work smarter, not harder
Highlighted
Honored Contributor

Re: Help required in one Expression

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

Highlighted
Honored Contributor

Re: Help required in one Expression

Let me give it a try.

Highlighted
MVP
MVP

Re: Help required in one Expression

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

Highlighted
Honored Contributor

Re: Help required in one Expression

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

Highlighted
MVP
MVP

Re: Help required in one Expression

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))) 

View solution in original post

Highlighted
Honored Contributor II

Re: Help required in one Expression

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))

Learning never stops.
Highlighted
Honored Contributor

Re: Help required in one Expression

Hi @tresesco

Thanks, it worked.

Can you please explain ?