Table help

[joshrussin]

hey guys,

Sums are not adding correctly.

This is what the table looks like

This is what the database looks like

For my table,

Today's WIP = sum({$< Date={"$(=FirstWorkDate(ConvertToLocalTime(Date(now()), 'GMT-05:00'),1,$(vHolidays)))"}>} WIP)

MTD WIP =

sum({$< Month = {"$(=Month(FirstWorkDate(ConvertToLocalTime(Date(now()), 'GMT-05:00'),1,$(vHolidays))))"},

Year = {"$(=Year(FirstWorkDate(ConvertToLocalTime(Date(now()), 'GMT-05:00'),1,$(vHolidays))))"}>} WIP)

Last 30 Days WIP = SUM({<Date={"$(='>=' & date(today()-30))"}>} WIP)

Last 90 Days WIP = SUM({<Date={"$(='>=' & date(today()-90))"}>} WIP)

For some reason the MTD is adding up everything in WIP and not just current month.

[sunny_talwar]

How does your Month field look like? How is it created in the script? Using Month function in the script?

[joshrussin]

LOAD

    Date_Dash,

    Month(Date_Dash) as Month,

    Num(Month(Date_Dash)) as MonthNum,

    Year(Date_Dash) as Year,

    MakeDate(year(Date_Dash)) as vCY,

    MakeDate(month(Date_Dash)) as vCM

FROM [lib://Data files/OB_Summary_Data.xlsx]

(ooxml, embedded labels, table is OB_Dash_Data);

I have it for a separate excel file, so I should make a new one for this file and call it like Month2?

[sunny_talwar]

I have it for a separate excel file, so I should make a new one for this file and call it like Month2?

Not sure I follow this? a separate excel file for what?

[joshrussin]

I have a few separate data sources.

one called status

and another called summary

I have connected only the summary in the script, the others I just have them linked.

but for my connection for summary, I have this

LOAD

    Date_Dash,

    Month(Date_Dash) as Month,

    Num(Month(Date_Dash)) as MonthNum,

    Year(Date_Dash) as Year,

    MakeDate(year(Date_Dash)) as vCY,

    MakeDate(month(Date_Dash)) as vCM

FROM [lib://Data files/OB_Summary_Data.xlsx]

(ooxml, embedded labels, table is OB_Dash_Data);

[sunny_talwar]

Is Status and Summary linked to each other in a way that date selections flow through to the Status data?

[joshrussin]

I figured it out. I just linked all the data in the scripts rather that individually loading. After that I just made month for that data into Month_RD. It is working now. Thanks!