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: 
Sara_3
Creator
Creator

SUM the output of the expression

I have a Output weeks column and the set Analysis is below :

Output Weeks
12
12

 

Set Analysis:

only( { < [Category Type]= {'1'}, Output_Weeks= { "=max({<[Category Type]= {'1'}>} [Deadline Date])< Min({<[Category Type]= {'1'}>}[Creation Date])
AND min({<[Category Type]= {'1'}>} [Creation Date]) < Min ({<[Category Type]= {'1'}>}[Binding Date])" } > }Output_Weeks )

when I replaced the ONLY to SUM I got some random value.

Requirement:

need to SUM of the above set analysis means the output should be 12+12 = 24 in the KPI or Text box.

Thanks.

Labels (4)
7 Replies
Or
MVP
MVP

As you already tried, if you want the values to be summed you need to use sum() rather than Only(). If the result is not what you expect, this is likely because the underlying values are not what you provided and there are actually more than just these two rows. I'd suggest checking your source to ensure that it does in fact consist of what you expect. You could also troubleshoot by replacing the Only() with Count() - this will tell you how many underlying rows you're actually reading from, and if that number isn't two, there's probably some form of duplication.

Sara_3
Creator
Creator
Author

thanks , when I used COUNT it gave me 50 for each like 50 + 50 = 100.

Sara_3
Creator
Creator
Author

is there another way to do this ?

Or
MVP
MVP

Iif your data is actually two rows, both have a value of 12, and you use sum, you'll get a result of 24. If you are getting a different result, then your underlying data is not actually two rows of 12, but I have no way of knowing what it is you might be dealing with because in your sample data, it's just two rows of 12. As I suggested, you could use Count() to see if there's more than two rows, and you could use Concat() instead of Only() to get a list of all the values that are being included in this calculation.

Sara_3
Creator
Creator
Author

Thanks!

PrashantSangle

did you try with distinct 

like Sum(distinct yourexpression)

or try with AGGR()

like SUM(AGGR(yourExpression,dimesion_which_you_have_in_chart))

 

Regards,

Prashant Sangle

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Sara_3
Creator
Creator
Author

Yes, I tried SUM with distinct and aggr() , in both cases I got 12. 

Aggr expression:

sum(aggr(only( { < [Category Type]= {'1'}, Output_Weeks= { "=max({<[Category Type]= {'1'}>} [Deadline Date])< Min({<[Category Type]= {'1'}>}[Creation Date])
AND min({<[Category Type]= {'1'}>} [Creation Date]) < Min ({<[Category Type]= {'1'}>}[Binding Date])" } > }Output_Weeks ), Output_Weeks))

Thanks.