topic Re: Defining YTD/MTD Set Analysis expression through variables in Edit Script in QlikView

Defining YTD/MTD Set Analysis expression through variables in Edit Script

umartareen — Wed, 05 Oct 2016 05:48:01 GMT

Hi All,

I have a scenario where I need to define my YTD expressions in the edit script using variables.

Here is what I am doing and I am not getting the output.

Variable / Expression :

LET vFLM_Amount = 'sum({<[Fiscal Year] = {"$(vMaxYear)"}, [Period Number] = {">=$(vMinMonth) <=$(vMaxMonth)"}>} [Amount])';

where :

LET vMaxYear = 'max([Fiscal Year])';

LET vMinMonth = 'MinString([Period Number])';

LET vMaxMonth = 'MaxString([Period Number])';

When I try the same expression in a text object, then it works except that I have to replace the double (") with single quotes ('), as the single quotes throw an error when used through edit script.

How can this be achieved ? Help will be appreciated !!

Thanks,

Umar

Re: Defining YTD/MTD Set Analysis expression through variables in Edit Script

adamdavi3s — Wed, 05 Oct 2016 10:02:45 GMT

Hi Umar,

Don't use " (double quotes) use '' (double apostrophe)

LET vFLM_Amount    = 'sum({<[Fiscal Year] = {$(''vMaxYear)''}, [Period Number] = {''>=$(vMinMonth) <=$(vMaxMonth)''}>} [Amount])';

Re: Defining YTD/MTD Set Analysis expression through variables in Edit Script

sunny_talwar — Wed, 05 Oct 2016 10:09:49 GMT

May be try this:

LET vFLM_Amount = '=Sum({<[Fiscal Year] = {"$' & '(vMaxYear)"}, [Period Number] = {">=$' & '(vMinMonth) <=$' & '(vMaxMonth)"}>} [Amount])';

Dollar sign has the tendency to get evaluated in the script and in order to avoid that, break your expressions into parts and see if that works

Re: Defining YTD/MTD Set Analysis expression through variables in Edit Script

umartareen — Wed, 05 Oct 2016 11:54:26 GMT

I worked the solutions provided and unfortunately they do not yirlf the output.

Thanks,

Umar

Re: Defining YTD/MTD Set Analysis expression through variables in Edit Script

adamdavi3s — Wed, 05 Oct 2016 11:58:24 GMT

Actually I remeber this from before, try this:

LET vFLM_Amounttmp = 'sum({<[Fiscal Year] = {~(''vMaxYear)''}, [Period Number] = {''>=$(vMinMonth) <=$(vMaxMonth)''}>} [Amount])';

LET vFLM_Amount = "=replace([vFLM_Amounttmp],'~','$')";

Re: Defining YTD/MTD Set Analysis expression through variables in Edit Script

umartareen — Wed, 05 Oct 2016 12:04:54 GMT

Yield*

Re: Defining YTD/MTD Set Analysis expression through variables in Edit Script

Wed, 05 Oct 2016 12:17:02 GMT

Hi Umar,

Might be it will useful to you.

LET vFLM_Amount = '=Sum({<[Fiscal Year] = {"$' & '(vMaxYear)"}, [Period Number] = {">=$' & '(vMinMonth) <=$' & '(vMaxMonth)"}>} [Amount])';

Re: Defining YTD/MTD Set Analysis expression through variables in Edit Script

sunny_talwar — Wed, 05 Oct 2016 12:30:52 GMT

How about without the equal sign

LET vFLM_Amount = 'Sum({<[Fiscal Year] = {"$' & '(vMaxYear)"}, [Period Number] = {">=$' & '(vMinMonth) <=$' & '(vMaxMonth)"}>} [Amount])';

Re: Defining YTD/MTD Set Analysis expression through variables in Edit Script

umartareen — Wed, 05 Oct 2016 12:57:37 GMT

Sunny, Thanks for the immediate responses ! It still doesn't work !!

Thanks,

Umar

Re: Defining YTD/MTD Set Analysis expression through variables in Edit Script

adamdavi3s — Wed, 05 Oct 2016 13:05:09 GMT

This works for me, see attached

LET vFLM_Amounttmp = '=sum({<[Fiscal Year] = {~(''vMaxYear)''}, [Period Number] = {''>=~(vMinMonth) <=~(vMaxMonth)''}>} [Amount])';

LET vFLM_Amount = replace([vFLM_Amounttmp],'~','$');

Re: Defining YTD/MTD Set Analysis expression through variables in Edit Script

sunny_talwar — Wed, 05 Oct 2016 13:09:38 GMT

Just tried mine on adamdavi3s‌'s sample. Seems to work as well

LET vFLM_Amounttmp = '=sum({<[Fiscal Year] = {~(''vMaxYear)''}, [Period Number] = {''>=~(vMinMonth) <=~(vMaxMonth)''}>} [Amount])';

LET vFLM_Amounttmp1 = '=Sum({<[Fiscal Year] = {"$' & '(vMaxYear)"}, [Period Number] = {">=$' & '(vMinMonth) <=$' & '(vMaxMonth)"}>} [Amount])';

LET vFLM_Amount = replace([vFLM_Amounttmp],'~','$');

Re: Defining YTD/MTD Set Analysis expression through variables in Edit Script

adamdavi3s — Wed, 05 Oct 2016 13:18:07 GMT

It's personal preference I think.... I like the two step process as I think for bit formula is is easier to understand than using extra &

Re: Defining YTD/MTD Set Analysis expression through variables in Edit Script

sunny_talwar — Wed, 05 Oct 2016 13:22:15 GMT

adamdavi3s‌ - You are absolutely right . I just wanted to point out that the other solution also works, But yes, the OP and other people who visit this thread are free to choice whichever they think is a better option.

Best,

Sunny

Re: Defining YTD/MTD Set Analysis expression through variables in Edit Script

adamdavi3s — Wed, 05 Oct 2016 13:23:53 GMT

Oh totally, I couldn't agree more!

variety is the spice of life and its great to have options!!

Re: Defining YTD/MTD Set Analysis expression through variables in Edit Script

sunny_talwar — Wed, 05 Oct 2016 16:44:18 GMT

How is this different then what I have already proposed my friend?

Re: Defining YTD/MTD Set Analysis expression through variables in Edit Script

umartareen — Thu, 06 Oct 2016 10:33:38 GMT

Hi All,

Thanks for your replies, appreciate it !

Not sure why the solutions provided in the forum are not working for me. It could be that I am going wrong somewhere while applying them ! Or could there be a version issue ? Just curious.

If nothing else works, I will have to make these variables on my front end !

Regards,

Umar

Re: Defining YTD/MTD Set Analysis expression through variables in Edit Script

sunny_talwar — Thu, 06 Oct 2016 10:55:06 GMT

Umar -

May be if you can replicate the same issue in a sample file and share it with us? That ways we can actually see the issue and will know what might be going wrong. Right?

Re: Defining YTD/MTD Set Analysis expression through variables in Edit Script

umartareen — Thu, 06 Oct 2016 12:01:46 GMT

Sunny, your solution works perfectly. Its actually a data issue on my end and so was not getting the output.

Thank you very much,

Regards,

Umar