topic Re: Set Analysis comparing 2 date fields in App Development

Set Analysis comparing 2 date fields

joey_lutes — Thu, 08 Dec 2016 17:58:47 GMT

Can anyone tell me what's wrong with this code? (site.VerifiedDate and ReferenceDate are both date fields in different tables)

Sum({$<site.VerifiedDate={">=$(ReferenceDate)"},site.AddedDate={"<=$(ReferenceDate)"}>}site.Counter)

Date Format has been verified and works

Thanks!

Re: Set Analysis comparing 2 date fields

sunny_talwar — Thu, 08 Dec 2016 18:13:12 GMT

There is only one value selected for ReferenceDate?

Re: Set Analysis comparing 2 date fields

joey_lutes — Thu, 08 Dec 2016 18:33:01 GMT

Correct.

Re: Set Analysis comparing 2 date fields

sunny_talwar — Thu, 08 Dec 2016 18:52:48 GMT

Try this:

Sum({$<site.VerifiedDate={">=$(=ReferenceDate)"},site.AddedDate={"<=$(=ReferenceDate)"}>}site.Counter)

Re: Set Analysis comparing 2 date fields

joey_lutes — Thu, 08 Dec 2016 19:33:28 GMT

That worked!

Thanks as always! You're the S-A KING!

Re: Set Analysis comparing 2 date fields

sunny_talwar — Thu, 08 Dec 2016 19:34:26 GMT

S-A Queen?? What's that??

Re: Set Analysis comparing 2 date fields

joey_lutes — Thu, 08 Dec 2016 20:07:05 GMT

Set Analysis of course

Re: Set Analysis comparing 2 date fields

sunny_talwar — Thu, 08 Dec 2016 20:09:29 GMT

S-A King you mean....

Re: Set Analysis comparing 2 date fields

joey_lutes — Thu, 08 Dec 2016 20:17:02 GMT

My sincere apologies. King it is! (very small picture). I'll update the post accordingly.

Begging forgiveness!!

Thanks again for the help.

Re: Set Analysis comparing 2 date fields

sunny_talwar — Thu, 08 Dec 2016 20:39:04 GMT

no worries

Re: Set Analysis comparing 2 date fields

joey_lutes — Fri, 09 Dec 2016 00:41:38 GMT

Would the syntax be the same if I'm not using < or > only =?

Re: Set Analysis comparing 2 date fields

sunny_talwar — Fri, 09 Dec 2016 00:52:18 GMT

Like this:

Sum({$<site.VerifiedDate={"$(=ReferenceDate)"}, site.AddedDate={"$(=ReferenceDate)"}>}site.Counter)

Re: Set Analysis comparing 2 date fields

joey_lutes — Fri, 09 Dec 2016 03:49:16 GMT

I'm sorry - that last one didn't work. I simplified it down, but no dice.

Sum({$<site.AddedDate={"$=(siteDate)"}>}site.Counter)

Thoughts? They make this stuff so complicated. Ironically, my goal is . . .

Sum({$<site.AddedDate={"$=(siteDate)-1"}>}site.Counter)

though do you put the -1 after the ) or the " lol. I keep trying

Re: Set Analysis comparing 2 date fields

sunny_talwar — Fri, 09 Dec 2016 10:02:14 GMT

Are you still selecting a single siteDate? Also, is siteDate and site.AddedDate dates or are they TimeStamps? If they are TimeStamps, then this expression is not going to work.

Re: Set Analysis comparing 2 date fields

joey_lutes — Fri, 09 Dec 2016 15:23:59 GMT

1) no, sorry - forgot to mention that. see: Compare Dates in Timeline

I'll move the question over there - thanks so much again for your help.

2) they are indeed dates not timestamps, format verified.

Re: Set Analysis comparing 2 date fields

joey_lutes — Sun, 11 Dec 2016 20:58:32 GMT

Sorry to be such a pain . . . but . . .

I'm attempting to select 1 date (ReferenceDate) and have a count of items (sum (item.counter) where its 'Verified' or 'Added' date = the ReferenceDate selected. It's important to note that these 2 dates are NOT linked in the data model, however their format is identical.

I'm able to get a straight table to display accurate data, but KPI boxes are failing me.

Sunny has been great with the set analysis help, but for some reason it's not displaying accurate data.

Here are some screenshots of the data. and UI.

Within the sites table, the load looks like this:

11-10-2010, 2 sites. both added dates and verified dates = 11-10-2010

11-11-2010, 2 sides added, one new, one original one. One of the originals not in the load = removed.

In the UI, this is what it looks like:

Notice date selected = 11-11-2016

The tables and bar charts are displaying accurate data.

The table code is: if(site.AddedDate] = ReferenceDate, [site.id]) (similar with 'Removed' table)

For the KPI boxes,

Left box:

sum({$<[site.VerifiedDate]={"$(=ReferenceDate)"}>}[site.counter])

That should give me a count of all sites that were verified as of day 'x'. Host sure why it's showing 1.

The correct number is 3.

Right box:

sum({$<[site.AddedDate]={"=$(=ReferenceDate)"}>}[site.counter])

That should give me the total sites added on day 'x'. Notice it's counting every site that's ever been added. The correct number is 1 (only 1 unique site added)

One other thing to note, while 'ReferenceDate' and 'site.AddedDate' are not linked in the model, the 'site' entity does have a common datefield called siteDate which links with 'site.AddedDate'. if I replace 'ReferenceDate' with 'siteDate', the results do not change, so I don't think it's a relationship issue.

Any help would be greatly appreciated.

Regards

Re: Set Analysis comparing 2 date fields

sunny_talwar — Sun, 11 Dec 2016 21:36:42 GMT

Would it be possible for your to share a sample of your application to look at this? It sort of makes sense, but it would be easier to pin point any issue by having a closer look at this.

Best,

Sunny

Re: Set Analysis comparing 2 date fields

joey_lutes — Mon, 12 Dec 2016 16:06:13 GMT

Here you go.

I have created some counter tables (V=Verified, A=Added, R=Removed). Basically, they create a total of each, by date for UI simplicity.

If you'll notice the 'Inactive' dimension in the Active/Inactive KPI on Activity Detail worksheet is producing incorrect data by using the set analysis (select 11-10-2016 as date, shows 1 inactive, should show 0)

Then you'll notice the dates on the bar charts, line charts on the Activity and State Detail worksheets have weirdness to them.

Thanks so much!

https://dl.dropboxusercontent.com/u/44401670/VINTA%20v3.0.qvf

Re: Set Analysis comparing 2 date fields

neerajthakur — Thu, 23 Dec 2021 05:10:12 GMT

Hi, Sunny

Please help me with this.

count({<current_license_flag={'1'},status_id={'8'},[Expiry Date]={">=$(=vToday()"}>}distinct ref_id)

vToday is Num(Today())

Expiry Date is in timestamp format. How can I compare both date.