topic Re: Set Analysis in calculated variable in QlikView

Set Analysis in calculated variable

Anonymous — Mon, 12 Sep 2016 08:55:42 GMT

Hi Guy,

I have a variable wich is set and calculated as Include statement in the script

vProject# = sum({$<[ForecastCategoryName] = {'Pipeline'},DateType = {'Snapshot Date'}>}[# Opportunity])

And the front end expression of the chart is defined as $(vProject#)

But now i have several dimensions like country and year which a want to slice and dice trough this calculation in the frond end

How do i write this properly?

sum({$<[Year] = {'2016'},($(vProject#)) will not work. Pls advice

Thanks!

Robbie

Anonymous — Mon, 12 Sep 2016 09:01:33 GMT

Try this

$(=only({$<[Year] = {'2016'}>}vProject#)

Regards,

Greeshma

Anonymous — Mon, 12 Sep 2016 09:12:31 GMT

Nope,

No resuls

miguelbraga — Mon, 12 Sep 2016 09:14:38 GMT

Hey there,

First of all, your set analysis expression is wrong and invalid and it will return null. Try maybe, tweak it a bit like this:

sum({$<[ForecastCategoryName] = {'Pipeline'},DateType = {'Snapshot Date'}, [Year] = {'2016'} >} [# Opportunity])

With these you can do what you want

Regards,

Anil_Babu_Samineni — Mon, 12 Sep 2016 09:15:24 GMT

Perhaps This

=sum({$<[Year] = {'2016'} $(vProject#))

OR For testing check this

Aggr(sum({$<[Year] = {'2016'} $(vProject#)),[ForecastCategoryName])

Would you able to provide sample

jonathandienst — Mon, 12 Sep 2016 09:30:16 GMT

No matter how you want to massage your expression

=sum({$<[Year] = {'2016'},($(vProject#))

you will not get that to work, as this a nested Sum statement which is not allowed.

You can go with miguelbraga‌'s solution above, or you could try

=Sum({$<[Year] = {'2016'}>} Aggr($(vProject#), Year, chartdim1, chartdim2, ...))

Where chartdim1, chartdim2 is comma separated list of all (one or more ) the chart dimensions.