Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
catunoho
Partner
Partner

SUM + IF

Hello all,

have a big problem with calculating a percentage of the two marked columns. I would like to show the percentage in a Text Box but since the column on the right side is calculate with an if clause, QlikView won't let me do this.

The column "Zielererichung" looks as follows:

if((sum(tt_rueck) / sum(TKE_Wert)) >= 1, sum(tt_anwesend), 0)

Column "Anwesend" is just: sum(tt_anwesend)

If I create a text box and try to calculate the percentage:

=num(100 / (sum(if((sum(tt_rueck) / sum(TKE_Wert)) >= 1, sum(tt_anwesend), 0))) / sum(tt_anwesend))

I get the message:

Error: nested aggregation not allowed

TKE.jpg

I can not get the total fro "Zielerreichung" from the script, the result does not match with what I get in the chart

any idea's

Thanks

Stefan

1 Solution

Accepted Solutions
simondachstr
Luminary
Luminary

Can you validate the below formula in a text box.

=num((sum(aggr(if((sum(tt_rueck) / sum(TKE_Wert)) >= 1, sum(tt_anwesend), 0), Bereich, Team, Benennung)) / sum(total tt_anwesend)),'#.##0%')

View solution in original post

6 Replies
Not applicable

You can maybe try:

= Num(100/  Sum(if(Sum(tt_reck)/sum(TKE_Wert) >=1, tt_anwesend,0))   /   Sum(tt_anwesend) )

CAn you attach a sample file if it doens't work.

Thanks

catunoho
Partner
Partner

Sorry did not help. Here is the sample file... hope the german description are not a problem

jagan
MVP & Luminary
MVP & Luminary

Hi Stefan,

Try like this

LOAD

*,

if((sum(tt_rueck) / sum(TKE_Wert)) >= 1, tt_anwesend, 0) AS Zielerreichung

FROM DataSource;

Now in chart you directly use =Sum(Zielerreichung) and check whether values are correct.

Hope this helps you.

Regards,

Jagan.

simondachstr
Luminary
Luminary

Can you validate the below formula in a text box.

=num((sum(aggr(if((sum(tt_rueck) / sum(TKE_Wert)) >= 1, sum(tt_anwesend), 0), Bereich, Team, Benennung)) / sum(total tt_anwesend)),'#.##0%')

View solution in original post

catunoho
Partner
Partner

Hi Jagan,

tried this before but I get different (wrong) values if I use the if clause in the script.

Regards

Stefan

catunoho
Partner
Partner

Hello Martin,

that's it...works perfect !!! Thanks a lot