Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
denareavis
Creator
Creator

Concat and GetFieldSelections to use in Set Analysis?

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

1 Solution

Accepted Solutions
krishna_2644
Specialist III
Specialist III

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

1.PNG

In your case, try this :

vCurrentSelectedMonth

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

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

                        )



View solution in original post

4 Replies
krishna_2644
Specialist III
Specialist III

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

1.PNG

In your case, try this :

vCurrentSelectedMonth

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

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

                        )



View solution in original post

rwunderlich
Luminary Alumni
Luminary Alumni

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

denareavis
Creator
Creator
Author

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!

denareavis
Creator
Creator
Author

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.