Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Expression totals change when selecting different month

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 Column1qv1.jpg

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

qv2.jpg

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

qv3.jpg

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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.



talk is cheap, supply exceeds demand

View solution in original post

2 Replies
Gysbert_Wassenaar

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.



talk is cheap, supply exceeds demand
Not applicable
Author

thank you that solves it