CODEWITHSUNDEEP
spotfiretibco
user1630575
user1630575

Reputation: 171

Find date difference based on condition

I have a table where data may be provided on monthly or yearly basis. The table data looks something as below:

Item        Date        Name
Class 1     12/31/2010  David
Class 1     12/31/2011  David
Class 1     12/31/2012  David
Class 1     12/31/2010  Moses
Class 1     12/31/2011  Moses
Class 1     12/31/2012  Moses
Class 1     01/31/2012  Shelly
Class 1     02/28/2012  Shelly
Class 1     03/31/2012  Shelly
Class 1     04/30/2012  Shelly
Class 1     05/31/2012  Shelly
Class 1     06/30/2012  Shelly
Class 1     07/31/2012  Shelly
Class 1     08/31/2012  Shelly
Class 1     09/30/2012  Shelly
Class 1     10/31/2012  Shelly
Class 1     11/30/2012  Shelly
Class 1     12/31/2012  Shelly

Class 2     01/31/2012  Shelly
Class 2     02/28/2012  Shelly
Class 2     03/31/2012  Shelly
Class 2     04/30/2012  Shelly
Class 2     05/31/2012  Shelly
Class 2     06/30/2012  Shelly
Class 2     07/31/2012  Shelly
Class 2     08/31/2012  Shelly
Class 2     09/30/2012  Shelly
Class 2     10/31/2012  Shelly
Class 2     11/30/2012  Shelly
Class 2     12/31/2012  Shelly
Class 2     01/31/2012  David
Class 2     02/28/2012  David
Class 2     03/31/2012  David
Class 2     04/30/2012  David
Class 2     05/31/2012  David
Class 2     12/31/2011  Soni
Class 2     12/31/2012  Soni

For a combination of Name and Item, either the date difference can be monthly or yearly. I want to include a calculated column named Flag. The condition to set flag is, if users had entered monthly data then set value as Yes else No.

So the rows with Class 1 - Shelly and all rows of Class 2 excluding Soni should be set as Yes.

Can anyone please guide me on this ? If I try Over and Intersect, for some of the columns, result is blank row.

Upvotes: 0

Views: 338

Answers (1)

S3S
S3S

Reputation: 25122

Assuming you have at least 2 months of data for each Item / Name pairing, you can approach this two ways. Note, that unless you have 2 months of data you can't tell if, for the given year, you are receiving data monthly or yearly.

  1. Insert Calculated Column DatePart("year",[Date]) as [Year]
  2. Insert Calculated Column If(Count([Item]) OVER (Intersect([Name],[Item],[Year]))>1,"Yes","No") as [Flag]

Another expression that you may find useful will apply a rank / row number to each Year | Month | Item | Name pairing. Here is that expression which you can use to see how many months of data you have for that paring (using MAX()) or to do other aggregates / logical checks.

RankReal(Date(DatePart("year",[Date]),DatePart("month",[Date]),1),"asc",[Name],[Item],DatePart("year",[Date]),"ties.method=minimum") as [RowRank]

Upvotes: 1

Related Questions