topic Re: Concat and GetFieldSelections to use in Set Analysis? in QlikView

Concat and GetFieldSelections to use in Set Analysis?

dena_reavis — Sun, 04 Mar 2018 20:31:02 GMT

Dear Experts,

I need help getting this to work. (Or if you have a better suggestion! )

I had a variable for the current month where I need to assume a selection if there are none, or take the latest month selected if a user has several months selected...

vCurrentSelectedMonth = if(GetSelectedCount([Calendar Month]) =0, VCurrentCloseMonth, MaxString([Calendar Month]))

This was working fine, but my requirement has changed and now I need to show more than one month totals instead of the latest selected month total.

By the way, VCurrentCloseMonth= a single value calculated during load script, makes it easy to refer to the current closing month.

If I have Jan and Feb selected in [Calendar Month], I wanted the set analysis below to evaluate to be

[Calendar Month] = {'JAN','FEB'}, (or whatever the user selected)

So, I was trying to use concat and getfieldselections, but my result is missing the inside single quotes.

=if(GetSelectedCount([Calendar Month]) =0, VCurrentCloseMonth,

chr(39)&concat(distinct GetFieldSelections([Calendar Month]), chr(39) & chr(44) & chr(39)) & chr(39))

I was hoping the result would be 'JAN','FEB' but the result is 'JAN,FEB' without inside quotes and does not work in the set analysis below... so I have something wrong.

Does it matter that [Calendar Month] field is a dual value field? It has number values so I can sort it in the proper order as needed. Also, I am using 12.10 SR 8.

This is where I am using this variable:

sum({<[Calendar Month] = {"$(vCurrentSelectedMonth)"},

[Fiscal Year Nbr] = {"$(vCurrentSelectedFY)"}

$(v_Amount))

Any help is appreciated!

Thank you

Re: Concat and GetFieldSelections to use in Set Analysis?

Qrishna — Sun, 04 Mar 2018 23:10:55 GMT

=concat( distinct chr(39) & Date ,chr(39) &',') & chr(39)

In your case, try this :

vCurrentSelectedMonth

= if(GetSelectedCount([Calendar Month]) =0, VCurrentCloseMonth,

concat( distinct chr(39) & [Calendar Month],chr(39) &',') & chr(39)

)

Re: Concat and GetFieldSelections to use in Set Analysis?

rwunderlich — Mon, 05 Mar 2018 02:15:10 GMT

Why use set analysis at all? If you want the data associated with the user selections, you can use the default behavior of the product.

sum($(vAmount))

will sum the currently selected data.

-Rob

Re: Concat and GetFieldSelections to use in Set Analysis?

dena_reavis — Mon, 05 Mar 2018 17:03:16 GMT

Krishna Nagulapally, thank you! I plugged that in and the variable now shows the right text

This is the variable definition for vCurrentSelectedMonth

=if(GetSelectedCount([Calendar Month]) =0, VCurrentCloseMonth, concat( distinct chr(39) & [Calendar Month],chr(39) &',',[Calendar Month]) & chr(39))

and it returns this:

'JAN','FEB'

But my set expression doesn't calculate when two months are selected. It does work when only one month is selected.

here is an example

sum({<[Calendar Month] = {"$(vCurrentSelectedMonth)"},

[Fiscal Year Nbr] = {"$(vCurrentSelectedFY)"},

Event = {'Actuals'},

Currency,

[Forecast Type]

$(v_Amount))

Rob Wunderlich, you're right, I was just showing a simplified version of the expression. Above, I used an example that is a little more like the real thing.

So, now my question is, is the multiple month syntax wrong? I had assumed it would have to be just as if I had hard-coded it to two months in the set analysis. Is it how I reference the variable? "$(vCurrentSelectedMonth)"

Thank you,

All help is appreciated!

Re: Concat and GetFieldSelections to use in Set Analysis?

dena_reavis — Mon, 05 Mar 2018 17:59:24 GMT

Hi,

I tried some more ways of referring to the variable and removing the double quotes worked!

[Calendar Month] = {$(vCurrentSelectedMonth)},

Using that worked!!!

Thank you Krishna and Rob!!

P.s. I use the document analyzer nearly every week. I'm a fan.