Sumif in a crosstable (set analysis/aggr)?

[#1]

Hi everyone, I just wanted to ask if it is possible to replicate a sumif formula in a row of a straight table and get expressions outside of its dimension.

What I want is a formula that sums up all the values in a column where column a = 1 and column b = 2.

Just sum(if(condition, value)) seems to be limited by dimension. As far as set analysis or aggr function goes i'm not really sure where to start as they don't seem to deliver the expected result. I also have a crosstable format so limiting the number to a single row of that would be helpful. Thanks in advance!

[sunny_talwar]

Difficult to say anything before looking at a sample, can you share a sample?

[#3]

Hi,

sorry for the late reply. I have attached an example in Excel.

I would need one specific line to refer to the all values in a column given certain conditions.

[sunny_talwar]

I think what you need is something like this:

1)

Sum({<Group = {'G1'}, Exclude -= {'y'}>} Cost * Budget)

2)

Sum({<Group = {'G1'}, Exclude -= {'y'}>} [Cost Net] * Budget)

[#5]

Hi thanks for your answer, but it doesn't seem to result in the correct number.

I use a straight table and have the expression for cost like this:

if(Wildmatch(Product, 'Group*'), Sum({<Group = {'G1'}, Exclude -= {'y'}>} Cost * Budget), [Cost])

But it returns a 0. Even something like

if(Wildmatch(Product, 'Group*'), Sum({<Group = {'G1'}>} Cost), [Cost])

returns a 0 when put in the expression, seemingly being limited by the dimension. Is there a way to use values outside of the dimension scope?

[sunny_talwar]

Can you try this:

If(Wildmatch(Product, 'Group*'), Sum(TOTAL <Group>{<Group = {'G1'}, Exclude -= {'y'}>} Cost * Budget), [Cost])

[#7]

Unfortunately still a 0.

[sunny_talwar]

Would you be able to provide your qvw?

[#9]

Sorry for some reason I am unable to upload the qv file.

But all I did was load all the fields of the earlier attached excel after clearing out the formula and put the fields in a straight table with [Product] as Dimension and the rest as Expressions and the formula that you provided earlier in Cost.

[sunny_talwar · Accepted solution]

This?

Dimensions:

Product

Group

Exclude

Expressions:

1) If(Wildmatch(Product, 'Group*'), Sum({<Product = {"=Exclude <> 'y'"}>}TOTAL <Group> Cost * Budget), [Cost])

2) If(Wildmatch(Product, 'Group*'), Sum({<Product = {"=Exclude <> 'y'"}>}TOTAL <Group> [Cost Net] * Budget), [Cost])