Date with multiple values

[evansabres]

I have a field called 'add_datetime' which is formatted as m/d/yyy hh:mm

I am attempting to create a chart that uses the field 'num_seats' as the expression. However, the problem I am running into is that for 'add_datetime' there can be multiple instances of the same date occurring.

For example, if 50 seats sold on 1/1/2017, as part of 25 transactions, I want to be able to show that 50 seats sold on 1/1/2017 as opposed to all of the instances of seats selling on 1/1/2017.

[ahaahaaha]

Hi Evan,

May be add unique field at load source data for every deal?

RecNo() as ID_Deal

Regards,

Andrey

[evansabres]

I want the chart to show that on 1/1/2017 there were 50 seats sold, and then on 1/2/2017, 2 seats sold and down the line so that I can have a chart that shows the growth of sales over time. Currently, just leaving the date as is results in a chart that has as many instances of 1/1/2017 as there were transactions. That is what I am trying to avoid

[evansabres · Accepted solution]

Was able to find the correct response from a previous thread:

Do the following in load script:

DayStart(add_datetime)               AS          transDATE

Then in a separate table:

Calendar:

LOAD *

,date(monthstart(transDATE),'MMM YYYY') as [Created Month]

,date(yearstart(transDATE),'YYYY') as [Created Year]

;

LOAD date(fieldvalue('transDATE',recno())) as transDATE

AUTOGENERATE fieldvaluecount('transDATE')