Set Analysis Help

[#1]

Hello All. Pardon me if I go about this the wrong way, but this is my first discussion on QC. I'm brand new to QV and I'm trying to create a set analysis statement that will do the following:

Sum the current fiscal year sales where the two previous years did not have sales taking into account current selections. I have a variable called "FiscalCurrentYear" so I've been using "$(=FiscalCurrentYear - 2)" and "$(=FiscalCurrentYear - 1)" for the two previous years.

This is as far as I've gotten:

Sum({$<[Fiscal Year]={$(FiscalCurrentYear)} -
P({$<[Fiscal Year]={$(=FiscalCurrentYear - 1),$(=FiscalCurrentYear - 2)},[Sales Amount]={"<=0"}>}[Sales Amount])>}[Sales Amount])

...which isn't working.

Any help would be greatly appreciated.

[#2]

Kaushik, is there an easy way of exporting the object and data including all levels of the drill down? I have tried the export qvo but it only gives a 2d data snapshot.

[#3]

Thank you for the help, but with the amount of product categories we have, this would be very hard to maintain if I was able to make it work. But that was innovative thinking

[#4 · Accepted solution]

Ok. I've finally got it worked out. I was able to create the "New Business" field with the following:

Sum(

// Get all accounts from the entire current fiscal year

{$<[Ship To] = p({$<[Fiscal Year]={$(FiscalCurrentYear)}, Month={"*"}, [Sales Catalog Sub Section] = {"*"}>}[Ship To]) -

// Remove records that have sales from the previous year

p({$<[Fiscal Year]={$(=FiscalCurrentYear-1)}, Month={"*"}, [Sales Catalog Sub Section] = {"*"}>}[Ship To]) -

// Remove record that have sale from the year before that

p({$<[Fiscal Year]={$(=FiscalCurrentYear-2)}, Month={"*"}, [Sales Catalog Sub Section] = {"*"}>}[Ship To])

>} [Sales Amount])

This gives me the correct roll-up values at all levels of the drill down table. Hopefully this may help someone in the future. Thanks to those that did reply.