topic Re: How to put formulas into set modifiers in Set Analysis? in QlikView

How to put formulas into set modifiers in Set Analysis?

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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?

Lots of thanks.

Re: How to put formulas into set modifiers in Set Analysis?

Or — Tue, 04 Sep 2018 09:08:08 GMT

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

Re: How to put formulas into set modifiers in Set Analysis?

andrey_krylov — Tue, 04 Sep 2018 09:16:39 GMT

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

Re: How to put formulas into set modifiers in Set Analysis?

Anonymous — Tue, 04 Sep 2018 10:35:25 GMT

Hi Shoham,

I tried but it doesn't work.

Re: How to put formulas into set modifiers in Set Analysis?

jonathandienst — Tue, 04 Sep 2018 10:50:44 GMT

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

Re: How to put formulas into set modifiers in Set Analysis?

Anonymous — Tue, 04 Sep 2018 10:50:51 GMT

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

Re: How to put formulas into set modifiers in Set Analysis?

gawalimegha — Tue, 04 Sep 2018 11:05:08 GMT

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

Re: How to put formulas into set modifiers in Set Analysis?

andrey_krylov — Tue, 04 Sep 2018 11:09:06 GMT

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

Re: How to put formulas into set modifiers in Set Analysis?

Anonymous — Tue, 04 Sep 2018 11:21:15 GMT

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.

Re: How to put formulas into set modifiers in Set Analysis?

Anonymous — Tue, 04 Sep 2018 11:40:35 GMT

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)

Re: How to put formulas into set modifiers in Set Analysis?

andrey_krylov — Tue, 04 Sep 2018 11:49:12 GMT

Once again..)

Re: How to put formulas into set modifiers in Set Analysis?

Anonymous — Tue, 04 Sep 2018 12:02:59 GMT

Thanks Andrey. It hits right. I missed the double quote.

Pardon me for insisting on the correct one!

Re: How to put formulas into set modifiers in Set Analysis?

andrey_krylov — Tue, 04 Sep 2018 12:22:55 GMT

You are welcome.))