topic Formula in QlikView

Formula

smilingjohn — Wed, 25 Nov 2020 16:16:04 GMT

Hi Al,

Iam writng the below expression which is showing me an error , can someone please correct this

Please find the attachment as well

This is the expression Ima using :

=If

(

Day_mode = 1,

(

Date >= ' & Num(StartDate) & ' and Date <= ' & Num(EndDate) & ',

Date(Date, ' & chr(39) & 'D MMM' & chr(39) & '),

Null()

)',

(

Week_mode = 1,

(

Date_WeekEnd >= ' & Num(StartDate) & ' and Date_WeekEnd <= ' & Num(EndDate) & ',

Date(Date_WeekEnd, ' & chr(39) & 'D MMM' & chr(39) & '),

Null()

)',

(

Date_MonthNumber >= ' & Num(Year(StartDate)*100 + Month(StartDate)) & ' and Date_MonthNumber <= ' & Num(Year(EndDate)*100 + Month(EndDate)) & ',

Date(Date_MonthStart,' & chr(39) & 'MMM YYYY' & chr(39) & '),

Null()

)

Re: Formula

settu_periasamy — Wed, 20 Apr 2016 04:53:49 GMT

Hi,

Try this..

=If
(
Day_mode = 1,
'
If
(
Date >= ' & Num(StartDate) & ' and Date <= ' & Num(EndDate) & ',
Date(Date, ' & chr(39) & 'D MMM' & chr(39) & '),
Null()
)',
If
(
Week_mode = 1,
'
If
(
Date_WeekEnd >= ' & Num(StartDate) & ' and Date_WeekEnd <= ' & Num(EndDate) & ',
Date(Date_WeekEnd, ' & chr(39) & 'D MMM' & chr(39) & '),
Null()
)',

'If //I think single quote missing here
(
Date_MonthNumber >= ' & Num(Year(StartDate)*100 + Month(StartDate)) & ' and Date_MonthNumber <= ' & Num(Year(EndDate)*100 + Month(EndDate)) & ',
Date(Date_MonthStart,' & chr(39) & 'MMM YYYY' & chr(39) & '),
Null()
)'
)
)

Re: Formula

smilingjohn — Wed, 20 Apr 2016 04:59:38 GMT

Thanks Settu,

After implementing your expression it works and i have the line charts perfectly .

But when i place my mouse cursor over the line it shows correct date , week and month but somethign like this

Re: Formula

smilingjohn — Wed, 20 Apr 2016 05:12:16 GMT

Hi Settu,

Can you please tell me that when we put ' single quote the the if will not turn to blue ?

Why so ? and i trid with your above expression its giving the line charts for all week , day and mnth but when i plce my cursor over the line it shows above atached error , if the mode is in day it shows error: garbage after expression "D"=7jul

Re: Formula

settu_periasamy — Wed, 20 Apr 2016 05:17:11 GMT

Just a question.. Are you assigning this expression to any variable? or you are using this as a Calculated dimension?

i don't have an idea how your data looks.

Re: Formula

smilingjohn — Wed, 20 Apr 2016 05:19:50 GMT

Great

Yes exactly iam assigning this expression to the variable , and under calculated dimension iam taking that vairable as

$(date_dim)

Re: Formula

settu_periasamy — Wed, 20 Apr 2016 05:21:36 GMT

Can you give the Label name for this and check in your dimension?

Re: Formula

smilingjohn — Wed, 20 Apr 2016 05:27:27 GMT

Super , now it does work perfectly

Thanks settu

Re: Formula

settu_periasamy — Wed, 20 Apr 2016 05:31:19 GMT

Good to Know 🙂