topic Re: Monthly result in QlikView

Monthly result

joshrussin — Wed, 25 Nov 2020 16:16:04 GMT

The attached expression gives me the correct result of monthly gains/losses when a specific area is chosen in a filter. When none or all are selected I get an unknown number.

=(Sum({$<Date_Dash={"$(=FirstWorkDate(Today()-1,1,$(vHolidays)))"}, Status_Dash={'Completed'}>} Processed_Dash - Daily_Target_Dash))

(sum({$<Date_Dash={"$(=FirstWorkDate(Today()-1,1,$(vHolidays)))"}, Status_Dash={'Completed'}>}Ave_Cost_Dash))

vHolidays is a variable to account for holidays

I am trying to get the results in a text box.

The correct result when nothing or all are clicked should be "-3,188.95", I am actually getting "-8,240,678"

Re: Monthly result

sunny_talwar — Wed, 12 Jul 2017 17:08:35 GMT

Try this

=Sum(Aggr(

(Sum({$<Date_Dash={"$(=FirstWorkDate(Today()-1,1,$(vHolidays)))"}, Status_Dash={'Completed'}>} Processed_Dash - Daily_Target_Dash))

(sum({$<Date_Dash={"$(=FirstWorkDate(Today()-1,1,$(vHolidays)))"}, Status_Dash={'Completed'}>}Ave_Cost_Dash))

,Date_Dash, Program_Dash, Status_Dash))

Re: Monthly result

joshrussin — Wed, 12 Jul 2017 18:53:11 GMT

Thanks Sunny! That works

Can you explain to me how the aggr function is working here? I haven't used this one yet.

Re: Monthly result

sunny_talwar — Wed, 12 Jul 2017 22:23:30 GMT

Aggr() function is used for various reasons, but one of those is to get the sum of rows. So based on your chart, I think you wanted to get the sum of rows. So I used your expressions and wrapped it around with the Sum(Aggr())...

Sum(Aggr(Expression, ChartDimensionsSeparatedByComma))