Set Analysis groups

[pauldamen]

All,

I have a formula which looks if a company had revenue in 2012 and not in 2013. In that case it should be called "Stopped", otherwise it is "Active". The set analysis to achieve this looks like:

if(sum({< Year = {'2012'}>} Revenue)>0 AND sum({< Year = {'2013'}>} Revenue)=0, 'Stopped', 'Active')

This formula works great in a table. But what I need is this to be loaded in the script, so I can use the Stopped field in other set analysis and in a listbox so the stopped companies can be excluded from selection.

But even pasting this formula in a listbox doesn't work, also the set analysis doesn't work in the script.

Any ideas?

Regards,

Paul

[MK_QSL]

Set Analysis is not supporting in SCRIPT...

You need to change your script something like below..

Temp:

Load

     'Stopped' as Flag

     ID,

     Revenue

Resident TableName

Where Year = 2012 and Revenue > 0 or Year = 2013  and Revenue = 0 // or use Where Year = '2012' and Revenue > '0'

Join (YourFactTable)

Load * Resident Temp;

YourFactTableFinal:

Load *, If(Flag <> 'Stopped', 'Active') as Flag Resident YourFactTable;

Drop Tables YourFactTable, Temp;

[pauldamen]

Thanks for your help!

If I load the first temp table with the revenue and the year. Where do I load this from? My fact table or do I create a separate load from the source?

Regards,

Paul

[#4]

Hi Paul,

Use a Variable like

Let Var=if(sum({< Year = {'2012'}>} Revenue)>0 AND sum({< Year = {'2013'}>} Revenue)=0, 'Stopped', 'Active')

and in your list box Expression

Write

If(not isNull('Filed') , var,0) then it will show only active status companies

Regards

Nani

[pauldamen]

Hi Nani,

It is not taking set analysis in a let function in the script.

Regards,

Paul

[#6]

Create  variable at settings ---> Variable Over view .

[#7 · Accepted solution]

Hi Paul

Keep it simple in the script  - load an if statement inside the sum instead of set analysis to return the 2012 /2013 revenue.

CompaniesData:

Load CompanyID,

if( sum(if(Year = '2012',Revenue,0)) >0 and sum(if(Year = '2013',Revenue,0)) =0,

     'Stopped','Active')

from FactTable group by company;

Erica

[#8]

Ps I would probably use the same approach as you - keeping it in the script. Depending on your data structure this will keep things quicker in the app

[pauldamen]

Hi Erica,

How can I make the script take SUM? As far as I know you can't put the sum statement in a script (I get an error which tells me it is an invalid expression)

Regards,

Paul

[MK_QSL]

Please check updated file...