Set Analysis comparing 2 date fields

[joey_lutes]

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!

[joey_lutes]

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

[sunny_talwar]

Like this:

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

[joey_lutes]

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

[sunny_talwar]

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.

[joey_lutes]

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.

[joey_lutes]

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

[sunny_talwar]

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

[joey_lutes]

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

[neerajthakur]

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.