Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

How to put formulas into set modifiers in Set Analysis?

Hello,

I want to customize labels & expressions dynamically based on selection. The label will displays all the 12 months of the selected year.

I set up a set analysis to calculate amount for each month accordingly based on the label, but cannot put the formula into the modifiers in set expression (eg: in the snapshot, to replace "2015_Jan"). Does anyone know to do it? set modifiers.PNG

Lots of thanks.

1 Solution

Accepted Solutions
Highlighted
Specialist
Specialist

12 Replies
Highlighted
Specialist III
Specialist III

{<Year_month={$(=YourFormulaHere() }>} should work.

Highlighted
Specialist
Specialist

Hi, Angie, Or Shoham is right. I made some corrections, see attached

Highlighted
Contributor III
Contributor III

Hi Shoham,

I tried but it doesn't work.jan17.PNG

Highlighted

This all becomes so much easier if you convert the date fields into proper QV date (numeric) type during your load script...

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Contributor III
Contributor III

Hi Andrey, what expression did you make the adjustment? I couldn't locate it.

Highlighted
Contributor III
Contributor III

Hello Angie,

1) First create variable as

demoChk= =GetFieldSelections(YearMonth)

2)Then add below Expression in your chart

=sum({<YearMonth={'$(demoChk)'}>}[Fact.Total Profit])

3) Sample Output will be like this

Highlighted
Specialist
Specialist

Take another look, please. It seems I saved file with another name.

Highlighted
Contributor III
Contributor III

Hi Jonathan,

I convert it into Date format then apply the above instruction but could not get the output.

Convert in script:

Date(Date#([By Year] &'_'&[By Month],'YYYY_MMM'),'YYYY_MMM') as Year_month,

Adjust Expression

sum({<Year_month={$(=Year([Year_month]) & '_' &'Jan')}>}Rev)

What did I do wrong? It would be easier later on if I can do it right from the start.

Highlighted
Contributor III
Contributor III

Sorry Andrey, somehow I don't see the change in expression.

Can you pls show me here which should be adjusted to make it right? (in the attachment I converted into date format before loading the script)

sum({<Year_month={$(=Year([Year_month]) & '_' &'Jan')}>}Rev)