How to count certain values out of a column in a data set?

[#1]

Hi everyone!

I hope you're all having a good day. I am trying to count the number of rebates, co-ops, and warranties in a column that is filled with other things too. I need them all separated. I want the box to look like the attached image with the purple filled in with numbers. My best attempt is something like this:

=Money(count(if({<FiscalYear={'2015'}>}rebates)))

It seems to me like I would need to do a count if, select the column, and do a where clause to get the data count out, but I don't know how to assemble that. Any help would be greatly appreciated! Thanks!

[rubenmarin]

Hi Elizabeth, if you're using set analisys you don't need the "if":

=Money(sum({<FiscalYear={'2015'}>}rebates))

Count() returns the number of records counted, you want to Sum() the values?

[#3]

I want to count the values. One of the values is wty for warranty, which you can't sum. Another value code is 2 for co-op, but if I sum those I will get double the number of occurrences. I tried your formula with count (by the way, I just noticed money shouldn't be there because it's a count), and it didn't work. I just got 0. Thanks for responding though! Any other ideas?

[rubenmarin]

Ok, so you should have something like:

Count({<FiscalYear={'2015'}>}rebates)

This will count the values on the field rebates with' 2015' selected in FiscalYear, can you add 2 list boxes with fields FiscalYear and rebates, select '2015' in FiscalYear and check if there are any possible values in rebates Field.

[#5]

All right, I think I'm just majorly mixed up, and I apologize for being so unclear! The file is an Excel file called "FakeGMR". The column is called "Order reason". In order reason, there are 30ish codes I want to extract 2, 3, and wty separately. So what I've modified it to be, thanks to your help, is:

=count({<FakeGMR={'Order reason'}>3})

But this is wrong on several levels. Any ideas? Thank you so much!

[rubenmarin]

Hi maybe with this:

Count({<[Order reason]={'2','3'}>} [Order reason])

This is filtering the field [Order reason] for the values '2' and '3', then is counting the values on that field (you can use other field to the count)

[#7]

Didn't work Thanks so much for your help! Still came back as 0.

[rubenmarin]

Can you upload the document with sample data to check?

[ankit777]

use

for 2

count({<[Order reason]={'2'}>}[Order reason])

for 3

count({<[Order reason]={'3'}>}[Order reason])

for wty

count({<[Order reason]={'wty'}>}[Order reason])

[Anonymous]

=count({1<[FiscalYear] ={'2015'}, [OrderReason] = {'2'}>}OrderReason)   <---- if it corresponds to rebates, put this in the purple rebates box next to the the rebates label

=count({1<[FiscalYear] ={'2015'}, [OrderReason] = {'wty'}>}OrderReason) <----- this should work for warranty. Could be 'WTY', depends on what the value looks like exactly in your excel column.

Your dataset must be hooked up to the Master Calendar in order to properly calculate for FiscalYear = {'2015'}.