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: 
wonkymeister
Creator III
Creator III

Date Variable Set Analysis

Help please,

I need to subtract one date from another (which is fine)

But the date that i am subtracting from is in a variable (vPeriodEnd), and my expression isn't working (it works fine with a standard field, its the variable that is causing me the headache). The date held within the variable is ok, as is the format.

any ideas?

if(

     only({<

          month=

          ,year=

          ,absence = {'Sickness'}

          ,census_date={'>=$(=$(vPeriodStart))   <=$(=$(vPeriodEnd))'}

     >}$(vPeriodEnd))

-

     only({<

          month=

          ,year=

          ,absence = {'Sickness'}

          ,census_date={'>=$(=$(vPeriodStart))   <=$(=$(vPeriodEnd))'}

     >}start_date)

> 28, 'Yes', 'No')

11 Replies
sunny_talwar

What exactly is behind vPeriodEnd? Is it an expression or a hard coded value?

sibin_jacob
Creator III
Creator III

Please try the below expression.

if(

     only({<

          month=

          ,year=

          ,absence = {'Sickness'}

          ,census_date={">=$(=vPeriodStart)   <=$(=vPeriodEnd)"}

     >}$(vPeriodEnd))

-

     only({<

          month=

          ,year=

          ,absence = {'Sickness'}

          ,census_date={">=$(=vPeriodStart)   <=$(=vPeriodEnd)"}

     >}start_date)

> 28, 'Yes', 'No')

wonkymeister
Creator III
Creator III
Author

a date (dd/mm/yyyy)

the user gets to select an end date using a slider on the dashboard to choose the period that they wish to view.

so essentially im subtracting vPeriodEnd - the database field start_date

wonkymeister
Creator III
Creator III
Author

Thanks Sibin,

tried that already, doesnt work.

sunny_talwar

So, via a slider a value is set for the date? May be see if this works

if(

    only({<

          month=

          ,year=

          ,absence = {'Sickness'}

          ,census_date={'>=$(=$(vPeriodStart))  <=$(=$(vPeriodEnd))'}

    >} Date#($(vPeriodEnd), 'DD/MM/YYYY'))

-

    only({<

          month=

          ,year=

          ,absence = {'Sickness'}

          ,census_date={'>=$(=$(vPeriodStart))  <=$(=$(vPeriodEnd))'}

    >}start_date)

> 28, 'Yes', 'No')

vishsaggi
Champion III
Champion III

May be give it a try: using sunny's formatting around your variable in single quotes?

if(

     only({<

          month=

          ,year=

          ,absence = {'Sickness'}

          ,census_date={'>=$(=$(vPeriodStart))   <=$(=$(vPeriodEnd))'}

     >} Date#('$(vPeriodEnd)', 'DD/MM/YYYY'))

wonkymeister
Creator III
Creator III
Author

No, still not working. Proper head scratcher this one.

sunny_talwar

Would you be able to share a sample to show what exactly you are doing?

balabhaskarqlik

Is Variable declarations of: vPeriodStart, vPeriodEnd matched with Census_date?