Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Wrong total sum with set but ok with if

Hi

I´m confused but optimistic that someone of you can help me with this.

If I write a expression as combined set analys and if expression the value for each row gets right but the total sum missmatch. If I then do it only as a if expression it works.  It´s a pivot table.

Any suggestions and ideas are welcome. Really need to get this working! I think it maybe has something to do with the lack of a initial sum in the set/if expression.

 

If(Match(Konto, 3400), Sum({<Månad = {'*'}, NÄMND_ID = {65}, År = {$(År2)} >} -Utfall_Belopp*_Flagga_Drift*_Flagga_TransUtbetald),
  If(Match(Konto, 3411, 7921,7931,8597), Sum({<Månad = {'*'}, NÄMND_ID = {65}, År = {$(År2)}, A3 = {651, 652, 653} >} Utfall_Belopp*_Flagga_Drift*_Flagga_TransUtbetald),
   If(Match(Konto, 6011),Sum({<Månad = {'*'}, NÄMND_ID = {65}, År = {$(År2)}, Verksamhet = {80010, 81510, 91011, 91012, 91013, 91014, 91015, 91016, 91017, 91018, 91019} >} -Utfall_Belopp*_Flagga_Drift*_Flagga_TransUtbetald),
Sum({< Månad = {'*'}, NÄMND_ID = {65}, År = {$(År2)}, A3 = {653} >} -Utfall_Belopp*_Flagga_Drift*_Flagga_TransUtbetald))))

If I do it as as straight If the sums are ok but then the user has to do all the selection by it self and also we can´t get year 1, year 2 and year 3 side by side in the table

 

=Sum(If(Konto = 3400, (-Utfall_Belopp*_Flagga_Drift*_Flagga_TransUtbetald),
     If(Match(Konto, 3411,7921,7931,8597) and Match(A3, 651, 652, 653), (-Utfall_Belopp*_Flagga_Drift*_Flagga_TransUtbetald),
          If(Konto = 6011 and Match(Verksamhet, 80010, 81510, 91011, 91012, 91013, 91014, 91015, 91016, 91017, 91018, 91019), (- Utfall_Belopp*_Flagga_Drift*_Flagga_TransUtbetald),
               if(Match(Konto, 3400,3411,7921,7931,8597,6011) = 0 and A3 = 653, (-Utfall_Belopp*_Flagga_Drift*_Flagga_TransUtbetald)
)))))

3 Replies
Not applicable
Author

Ok. I get it to work with this aggr but then I can´t have any Year (År) selected. Anyone that can help me? The yearVariable in the set should select the wanted year and does so but only if I don´t have the year-field selected.

 

SUM(Aggr(
If(Match(Konto, 3400), Sum({<År =, Månad = {'*'}, A2 = {65}, År = {$(År2)} >} -Utfall_Belopp*_Flagga_Drift*_Flagga_TransUtbetald),
If(Match(Konto, 3411, 7921,7931,8597), Sum({<År =, Månad = {'*'}, A2 = {65}, År = {$(År2)}, A3 = {651, 652, 653} >} -Utfall_Belopp*_Flagga_Drift*_Flagga_TransUtbetald),
If(Match(Konto, 6011),Sum({<År =, Månad = {'*'}, A2 = {65}, År = {$(År2)}, Verksamhet = {80010, 81510, 91011, 91012, 91013, 91014, 91015, 91016, 91017, 91018, 91019} >} -Utfall_Belopp*_Flagga_Drift*_Flagga_TransUtbetald),
Sum({<År =, Månad = {'*'}, A2 = {65}, År = {$(År2)}, A3 = {653} >} -Utfall_Belopp*_Flagga_Drift*_Flagga_TransUtbetald)))),Konto, Aktivitet, År, OBJEKT_ID_TEXT))

Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

Put the set modifier with År in the outer sum too.

SUM({<År =, Månad = {'*'}, A2 = {65}, År = {$(År2)} >} Aggr(
If(Match(Konto, 3400), Sum({<År =, Månad = {'*'}, A2 = {65}, År = {$(År2)} >} -Utfall_Belopp*_Flagga_Drift*_Flagga_TransUtbetald),
If(Match(Konto, 3411, 7921,7931,8597), Sum({<År =, Månad = {'*'}, A2 = {65}, År = {$(År2)}, A3 = {651, 652, 653} >} -Utfall_Belopp*_Flagga_Drift*_Flagga_TransUtbetald),
If(Match(Konto, 6011),Sum({<År =, Månad = {'*'}, A2 = {65}, År = {$(År2)}, Verksamhet = {80010, 81510, 91011, 91012, 91013, 91014, 91015, 91016, 91017, 91018, 91019} >} -Utfall_Belopp*_Flagga_Drift*_Flagga_TransUtbetald),
Sum({<År =, Månad = {'*'}, A2 = {65}, År = {$(År2)}, A3 = {653} >} -Utfall_Belopp*_Flagga_Drift*_Flagga_TransUtbetald)))),Konto,Aktivitet, År, OBJEKT_ID_TEXT))


talk is cheap, supply exceeds demand
Not applicable
Author

Thanks for the suggestion but it din´t change much. Still have to unmark the field År (year) in the app.

Also tried to change the yearvariable and hardcoded the year but same result

So the problem now is to why the expression below don´t unmark/override the selected year and use the year selected in the set expression.

 

Sum({<Månad = {'*'}, År = {$(År0)}, A2 = {65}>} Aggr(
If(Match(Konto, 3400), -Sum(Utfall_Belopp*_Flagga_Drift*_Flagga_TransUtbetald),
If(Match(Konto, 3411, 7921,7931,8597), -Sum({<A3 = {651, 652, 653} >} Utfall_Belopp*_Flagga_Drift*_Flagga_TransUtbetald),
If(Match(Konto, 6011), -Sum({<Verksamhet = {80010, 81510, 91011, 91012, 91013, 91014, 91015, 91016, 91017, 91018, 91019} >} Utfall_Belopp*_Flagga_Drift*_Flagga_TransUtbetald),
-
Sum({<A3 = {653} >} Utfall_Belopp*_Flagga_Drift*_Flagga_TransUtbetald)))), Konto, Aktivitet, OBJEKT_ID_TEXT, År))