If statement

[joshrussin]

I am trying to add 'Kit Build' and 'Kit Build - Timberline' when Program_Dash = 'Kit Build', the result I am getting from this is just '-' in the column. Any Help?

=if (Program_Dash = {'Kit Build'},

((Sum(aggr(

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

        Year = {"$(=Year(FirstWorkDate(ConvertToLocalTime(Date(now()), 'GMT-05:00'),1,$(vHolidays))))"}, Program_Dash = {'Kit Build'}>} (Ave_Cost_Dash * Processed_Dash)))

        /(sum(vDays_Con))) * (Sum(vDay_in_M))

        ,Date_Dash, Program_Dash, Status_Dash)))

    +

    (Sum(aggr(

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

        Year = {"$(=Year(FirstWorkDate(ConvertToLocalTime(Date(now()), 'GMT-05:00'),1,$(vHolidays))))"}, Program_Dash = {'Kit Build - Timberline'}>} (Ave_Cost_Dash * Processed_Dash)))

        /(sum(vDays_Con))) * (Sum(vDay_in_M))

        ,Date_Dash, Program_Dash, Status_Dash))))

,

    (Sum(aggr(

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

Year = {"$(=Year(FirstWorkDate(ConvertToLocalTime(Date(now()), 'GMT-05:00'),1,$(vHolidays))))"}>} (Ave_Cost_Dash * Processed_Dash)))/(sum(vDays_Con))) * (Sum(vDay_in_M))

,Date_Dash, Program_Dash, Status_Dash))))

[sunny_talwar · Accepted solution]

May be this

=if (Program_Dash = 'Kit Build',

((Sum(TOTAL <Date_Dash, Status_Dash> aggr(

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

        Year = {"$(=Year(FirstWorkDate(ConvertToLocalTime(Date(now()), 'GMT-05:00'),1,$(vHolidays))))"}, Program_Dash = {'Kit Build'}>} (Ave_Cost_Dash * Processed_Dash)))

        /(sum(vDays_Con))) * (Sum(vDay_in_M))

        ,Date_Dash, Program_Dash, Status_Dash)))

    +

    (Sum(TOTAL <Date_Dash, Status_Dash> aggr(

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

        Year = {"$(=Year(FirstWorkDate(ConvertToLocalTime(Date(now()), 'GMT-05:00'),1,$(vHolidays))))"}, Program_Dash = {'Kit Build - Timberline'}>} (Ave_Cost_Dash * Processed_Dash)))

        /(sum(vDays_Con))) * (Sum(vDay_in_M))

        ,Date_Dash, Program_Dash, Status_Dash))))

,

    (Sum(aggr(

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

Year = {"$(=Year(FirstWorkDate(ConvertToLocalTime(Date(now()), 'GMT-05:00'),1,$(vHolidays))))"}>} (Ave_Cost_Dash * Processed_Dash)))/(sum(vDays_Con))) * (Sum(vDay_in_M))

,Date_Dash, Program_Dash, Status_Dash))))

[joshrussin]

Why doesn't this add the two? I am trying to get MTD revenue. This just shows Kit Build, not both kit build and kit build - timberline.

=if (Program_Dash = 'Kit Build',

((Sum(TOTAL <Date_Dash, Status_Dash> aggr(

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

        Year = {"$(=Year(FirstWorkDate(ConvertToLocalTime(Date(now()), 'GMT-05:00'),1,$(vHolidays))))"}, Program_Dash = {'Kit Build'}>} (Ave_Cost_Dash * Processed_Dash)))

        )

        ,Date_Dash, Program_Dash, Status_Dash)))

    +

    (Sum(TOTAL <Date_Dash, Status_Dash> aggr(

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

        Year = {"$(=Year(FirstWorkDate(ConvertToLocalTime(Date(now()), 'GMT-05:00'),1,$(vHolidays))))"}, Program_Dash = {'Kit Build - Timberline'}>} (Ave_Cost_Dash * Processed_Dash)))

        )

        ,Date_Dash, Program_Dash, Status_Dash))))

,

    (Sum(aggr(

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

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

,Date_Dash, Program_Dash, Status_Dash))))