If statement

[joshrussin]

I have this formula that works in a text box, but I am trying to get it to work in an IF statement, but only comes up with the total of 'Kit Build' instead of 'Kit Build' + 'Kit Build - Timberline'

Formula: it adds both together

((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))))

IF Statement: same formula for IF, just totals 'Kit Build'

=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))))

[Oleg_Troyansky]

Hi Joshua,

I admit that I haven't read everything in your formula, but it appears that the IF condition:

=if (Program_Dash = 'Kit Build',

that is verified outside of any aggregation, might be the cause of the problem. If you expected this condition to be verified for each data row, then it should be positioned within the aggregation functions, and preferably be replaced with Set Analysis.

The way it appears now, the formula will only work when there is one and only one available value for the field Program_Dash. As soon as multiple values are available, the condition will always return FALSE.

Cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming to Boston, MA this October!