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

[#2]

You need to add a "group by" to the end of the load statement

eg

Load A, sum(b) resident table group by A;;

Otherwise reply with your statement so we can see what the problem is?

[pauldamen]

Does it matter which field I use for the group by?

I do a group by company but it still gives an error, invalid expression.

See attached how that part of the script looks.

[MK_QSL]

Load Company,

         SUM(YourField)

Resident YourTableName

Group By Company;

[pauldamen]

That is exactly how I have it (little longer load script), see attached

Still returns the invalid expression

[MK_QSL]

Load CustomerID,

        InvoiceNumber,

        PartNumber

        SUM(Sales)

Resident TableName

Group By CustomerID, InvoiceNumber, PartNumber;

Hope you get it now...

[pauldamen]

I got it!!

When I do a second load only with the sum and the company and join that to the fact load it works.

Thanks both Manish and Erica for your quick help!

Regards,

Paul

[#8]

It's because you  need to group by the fields that do not have an aggregate expression (eg sum, avg, min...), otherwise qlikview does not know what to do with them.

Hence putting it in a separate, simpler table and  then either joining it back on or keeping it as a lookup is the easiest way

Left join (facttable) Load company, if(sum(if(....)) and sum(if(..))).. as expression resident facttable

Erica

[MK_QSL]

It is difficult to group by 5 or 10 items. Better to load only 2-3 fields and group by in second table and join these two tables...