Dropdown menu won't sum #s in table

[Breezy]

Hello,

I have a dropdown menu titled 'Location'. When I select Guam numbers appear in my table. When I select Argentina the appropriate numbers appear in the table. When I select them BOTH at the same time from the dropdown menu my table is empty! Why aren't my numbers adding up?

[Or]

Is your formula Sum(Population)? Or is it just Population? You need to tell the measure how to aggregate the results - sum, count, avg, etc.

[Breezy]

Awesome! I just modified each of my measures to use the sum function and they add up now.

One more issue on that....these are formatted as numbers (so I can use the sum function). However, I want some of the cells to state "N/A" (to indicate to my visitor that the information simply isn't available yet). Since they are in numerical format now and not text format, I cannot do that. For example, if my visitor only clicks "Guam" and Guam has "N/A" in my spreadsheet, is there a way to have N/A show up in the cell? It defaults to "$0" in the cell. (of course, if a person picked Guam with N/A and Canada with $5, it would still have to equate to $5.

Before changing this to use the 'sum' function I was able to have my cells include N/A. Of course, then they would not sum up!

IF ("Billing" = 'N/A', 'N/A', Money#("Billing", '#,###.'))as "Billing"

[Or]

You could try something along the lines of:

If(Only(Billing) = 'N/A','N/A',Money#(Sum(alt(Billing,0))))

This will sum all of the Billing values except for cases where all of the billing values for a certain cell are 'N/A'. It should also work in subtotals and totals.

[Breezy]

Where do I include my field there? If my field were, say, "rabbits", where would that go in here?

If(Only(Billing) = 'N/A','N/A',Money#(Sum(alt(Billing,0))))

Add tags

[Or]

Billing is supposed to be your field (or at least it's what you used in your original formula)..?

[Breezy]

Good point.

I'm going to look at a different app where I had the N/A part work. In that app I had:

If("frogprice" = 'N/A', 'N/A', Money#("frogprice", '#,###.'))as "frogprice",

When I mimic it though it does not work in my new app. All N/A turns to $0.

[Or]

Is frogprice actually the string 'N/A'? Are you summing the value later (in which case, you can't sum N/A)?

[Breezy]

It would look like this. So the sum worked until I messed it up by including an "N/A" there. I want to maintain the "N/A".  Might not be possible though...

[Or]

I don't know if that's N/A as a string or N/A as the result of a formula (and I'm not sure how Qlik reads the latter anyway)...

However, Sum(Anything) will never return 'N/A' because sum() returns a number and ignores non-numeric values. If you try to Sum('N/A') the result will be 0, and there's no way to tell the difference (post-sum) between that and Sum(0). I'm not sure if you're actually trying to sum something here or just adding two fields from the same row, though.