Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Can you try this with Dimension as Monthyear.
LOAD * ,
Month(report_date) & '' & Year(report_date) AS MonthYear
INLINE [
report_date,model,backlog
........
];
Expr:
= Sum(Aggr(FirstSortedValue(backlog, report_date), model, MonthYear))
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.
Use FirstSortedValue function. Still you have issues, please share a sample app.
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
Can post the result you want view from this example .
Is this what you looking for ??
I'm on the personal edition and cannot open that.
Thank you
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
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.
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?