Expression totals change when selecting different month

Tue, 24 Mar 2015 10:20:03 GMT

Hi,

i Have a report with 3 dimensions and 4 Expressions to calculate the "nett nett" for the last four months.

when i choose a different month the total changes. ie Column 1 was Sep , now column 2 is Sep but total is different

below image is SEP as first month in Column1

now i selected oct as month which moves sep to 2nd column total differs.

now when i select nov both change oct and sep. as below

column 1 expression

=(sum({<MonthYear = {"$(=MaxString(MonthYear))"}>} Price_Lens_Total_Net)

+sum({<MonthYear = {"$(=MaxString(MonthYear))"}>} Price_Semi_Net)

+sum({<MonthYear = {"$(=MaxString(MonthYear))"}>}Price_Addons_Total_Net)

+sum({<MonthYear = {"$(=MaxString(MonthYear))"}>}Price_Frame_Net))* SETTLEMENT_%

Column2 expression

=(sum({<Month =,MonthYear = {"$(=Date(AddMonths(Max(OrderDate),-1),'MMM-YYYY'))"}>} Price_Lens_Total_Net)

+sum({<Month =,MonthYear = {"$(=Date(AddMonths(Max(OrderDate),-1),'MMM-YYYY'))"}>} Price_Semi_Net)

+sum({<Month =,MonthYear = {"$(=Date(AddMonths(Max(OrderDate),-1),'MMM-YYYY'))"}>} Price_Addons_Total_Net)

+sum({<Month =,MonthYear = {"$(=Date(AddMonths(Max(OrderDate),-1),'MMM-YYYY'))"}>} Price_Frame_Net))* SETTLEMENT_%

Column3 expression

=(sum({<Month =,MonthYear = {"$(=Date(AddMonths(Max(OrderDate),-2),'MMM-YYYY'))"}>} Price_Lens_Total_Net)

+sum({<Month =,MonthYear = {"$(=Date(AddMonths(Max(OrderDate),-2),'MMM-YYYY'))"}>} Price_Semi_Net)

+sum({<Month =,MonthYear = {"$(=Date(AddMonths(Max(OrderDate),-2),'MMM-YYYY'))"}>} Price_Addons_Total_Net)

+sum({<Month =,MonthYear = {"$(=Date(AddMonths(Max(OrderDate),-2),'MMM-YYYY'))"}>} Price_Frame_Net))

* SETTLEMENT_%

i am multiplying the totals with the settlement discount precentage.

i have the totals mode set on sum of rows on all expressions.

any help in the right direction would be appreciated.

thank you

Kallie

Re: Expression totals change when selecting different month

Gysbert_Wassenaar — Tue, 24 Mar 2015 10:36:38 GMT

SETTLEMENT_% is a naked aggregation. Qlikview actually calculates only(SETTLEMENT_%). And that expression does not include the set modifiers you added to the sums. So your selections do influence the value of SETTLEMENT_% that is used in the calculations. Try adding a set modifier to only(SETTLEMENT_%) too.

Re: Expression totals change when selecting different month

Tue, 24 Mar 2015 10:53:38 GMT

thank you that solves it

topic Re: Expression totals change when selecting different month in QlikView

Expression totals change when selecting different month

Re: Expression totals change when selecting different month

Re: Expression totals change when selecting different month