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.

[sushil353]

Hi,

Try this:

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

or

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

I am assuming here the value of FiscalCurrentYear has values like 2011, 2012...

also please have a look on [Sales Amount]={"<=0"} what is the use of summing up sales amount where it is less than equal to zero...??

it should be :  [Sales Amount]={">=0"}

HTH

Sushil

[#3]

Hi Shushil, thank you for your reply. Unforutanely, what you propose won't get what I need. What I'm trying to show is "new business". In other words sales that didn't exist in the two years prior. So, lets say a user has a state selected and a product. I would need to see the sum sales that exist for the current fiscal year ONLY if there weren't any sales for the two prior years (i.e. FY11 = $0, FY12 = $0, FY13 = $1000) for that state and product. Does that make sense? Also, the <=0 was for any negative sales; in some case there may have been credits issued.

I hope I'm explaining that correctly. I believe the use of the p() function is what I need to use in my statement, but I'm not sure how yet. It's the weekend, I haven't put a lot of time in it

[#4]

I know this syntax isn't correct, but this is basically what I'm trying to accomplish:

sum(({<[Fiscal Year]={$(FiscalCurrentYear)},

[Sales Amount] = p({<(Year={$(FiscalCurrentYear - 1) AND [Sales Amount] = 0) AND (Year={$(FiscalCurrentYear - 2) AND [Sales Amount] = 0)} [Sales Amount]>}) [Sales Amount])

So, the sum of sales that in the previous two fiscal years were zero.

This is going to take help from someone smarter than me; Anyone?

[#5]

I'VE ALMOST GOT IT! Only problem left is that I have a drill down table that drills from Month to Sales Territory to Product. I'm only seeing my expression work at the Product level and not the two upper levels. WHY? Here's my expression:

Sum({$<[Fiscal Year]={$(FiscalCurrentYear)},

Product=E({$<[Fiscal Year]={$(=FiscalCurrentYear - 1)}> + $<[Fiscal Year]={$(=FiscalCurrentYear - 2)}>}Product)>}[Sales Amount])

Someone please help so I can move on!

[johnpaul]

What do you see at the upper levels?

Perhaps post a diagram of your data model, or a sample app.

[#7]

Thanks for the reply John. I get only zero values at the upper levels where I would expect to see the sum of the lower levels. Unfortunately, I can't share the file as it's company sensitive. Below are some screen shots:

Top level (Month):

Drill Down to second level of August:

Drill down into one of the territories:

[#8]

I'm dying here guys. This is my third day on this. Any help please??!!

[kaushiknsolanki]

Hi,

     Kindly upload the qvw file with sample data and expected output.

     This will give clear idea to understand your problem.

Regards,

Kaushik Solanki

[#10]

Okay, I think I can at least see what the problem is. What I think is happening is that at the sales territory and higher level, your expression is taking account of things across sales category sub sections. In short, because not all sub sections summed to 0 in 2011 and 2012, the 'new business' is 0.

I'm not quite sure what to do about it exactly... I'd probably start with rangesum(

Sum({$<[Fiscal Year]={$(FiscalCurrentYear)},

Product=E({$<[Fiscal Year]={$(=FiscalCurrentYear - 1)}> + $<[Fiscal Year]={$(=FiscalCurrentYear - 2)}>}Product)*Product={'A'}>}[Sales Amount]),

Sum({$<[Fiscal Year]={$(FiscalCurrentYear)},

Product=E({$<[Fiscal Year]={$(=FiscalCurrentYear - 1)}> + $<[Fiscal Year]={$(=FiscalCurrentYear - 2)}>}Product)*Product={'B'}>}[Sales Amount]),

Sum({$<[Fiscal Year]={$(FiscalCurrentYear)},

Product=E({$<[Fiscal Year]={$(=FiscalCurrentYear - 1)}> + $<[Fiscal Year]={$(=FiscalCurrentYear - 2)}>}Product)*Product={'C'}>}[Sales Amount]),

etc.)