Date Variable Set Analysis

[wonkymeister]

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

[sunny_talwar]

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

[sibin_jacob]

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]

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]

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]

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]

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?