How do I get the first entry for each month from a date field?

[#1]

My title probably isn't descriptive enough. There are a couple of reports run every month, and that data is dumped into a spreadsheet. That data has a date, model number, a "backlog count," and other fields. I need to make a chart that graphs that month's starting backlog total.

I'm sure it's something simple, and I'll hate myself once I get the answer, but my google-fu has failed me.

Thank you

[vishsaggi]

I believe You can use firstsortedvalue() function. Like

Did not try, please try and let me know or send us a sample app your are working on.

Load

Date,

ModelNumber,

BacklogCnt,

Firstsortedvalue(DISTINCT Backlogcnt, Date) AS FirstBacklogCnt

FROM .....;

Thanks,
V.

[trdandamudi]

Use FirstSortedValue function. Still you have issues, please share a sample app.

[#4]

Not quite what I'm looking for. I'm needing chart expressions. My data has weekly backlog reports for various models. My data structure is similar to this:

date,model,backlog

1/1/16,A,25

1/1/16,B,22

1/8/16,A,15

1/8/16,B,17

2/4/16,A,10

2/4/16,B,11

2/8/16,A,5

2/8/16/B,7

I need an expression or dimension or something that just gets the first report in each month, and sums the backlog.

Thank you

[maheshkuttappa]

Can post the result you want view from this example .

[vishsaggi]

Is this what you looking for ??

[#7]

I'm on the personal edition and cannot open that.

Thank you

[#8]

I would need a chart or table that has month and year along the X-axis and the sum of the backlog for all models on the Y-axis. So January would have a value of 47, and February would have a value of 21.

Thank you

[vishsaggi]

Oh ok. I tried my best but i am not sure if this suffice your request. Please let me know.

Add this in your script:

LOAD *,

     Month(date) AS Month;

LOAD * INLINE [

date,model,backlog

1/1/16,A,25

1/1/16,B,22

1/8/16,A,15

1/8/16,B,17

2/4/16,A,10

2/4/16,B,11

2/8/16,A,5

2/8/16,B,7

];

Then

Create a sheet object -> Chart -> bar chart ->

dim  - Month, Model

expr - Firstsortedvalue( aggr(sum(backlog),date), -Aggr(sum(backlog), model,date))

Let me know if this works.

Thanks,

V.

[#10]

Thank you! That seems to have worked (mostly). I used

=Month(Date) & ' ' & Year(Date)

as my dimension, and dropped the model from the expression. When I click the bar it seems to select every date from that month. That would be a problem with my dimension, correct?

And just to verify, since I'm not familiar with Aggr, that expression is returning the sum of the backlog for the minimum Date, correct? It's not grabbing the first backlog?