New monthly column with Sum of daily values

[Cathalc]

Hi all,

I got a really simple question but  I can't seem to make it work.

I've got values per day and I want to make a new table that sums these values per month. So I would have:

Jan2018 : 45
Feb2018 : 46
Mar2018  : 86
...

Here'z my code:

My code now compiles just fine but when I open the model viewer, the column wasn't created.

Does anyone know what I'm doing wrong?

Thanks!

[VishalWaghole]

Hi

No need to use second load statement.

Just use

Load TechMinute,
Date,
MonthName(Date) as YearMonth
From YourTable;

After that at front end level create one straight table with Dimension is YaerMonth and expression is sum(TechMinute)

Thanks and Regards,
Vishal Waghole

[Cathalc]

Hi,

Thanks for your answer. I should have added that I'm trying to calculate everything in the load editor. I want to do more complex calculations with multiple tables and it's impossible to do them front-end in a one-line expression.

Any ideas on how to do it at table level?

Thanks!

[dplr-rn]

It is possible to do really complex stuff on front end using set analysis.
Issue with creating such a summary table in script is that you may loose a lot of flexibility for further analysis.

i would recommend to try on front end first and only then go script level
but to answer your problem
you need group by to make it work correctly

SummaryTable:
load
YearMonth
,sum(TechMinute)
resident MonthlyPM
group by YearMonth

[Cathalc]

Hi,

Thank you for your answer this worked. But I see that the load editor is indeed more limited then I expected. I tried front-end first like you suggested but the expressions get to complex for what I currently know.

The problem is that I have two tables, one with flight hours and one with the number of defects of a certain aircraft type. (Both tables use the same dates). 

Both tables have a daily data range from 2016-2019. I then have to calculate the 3 month rolling averages for 1 year for both flight hours and defects. The resulting table then becomes for 1 year something like this:

jan2018: 45
feb2018: 65
Mar2018: 48
etc...

Once I have this I need to calculate the mean of these values and the standard deviation. This then results in a single value by applying the following formula: mean(values)+3*StDev(values)

Currently I have something like this for the calulation of the mean: avg(1000*((aggr(RangeAvg( above( count({$<year={2018}>} DefectType),0,3)),ACName))) / ((aggr(RangeAvg( above( sum({$<year={2018}>} FHTime)/60,0,3)),ACName))))

However, the expression doesn't return a value and also doesn't give an error so I'm a bit stuck 😉

Any help with this would help me too.

Thanks!