topic Re: $ sign issue inside set analysis in QlikView

$ sign issue inside set analysis

Fri, 06 Feb 2015 12:45:32 GMT

Hi,

I have the below expression in chart

=sum({<SpendYear={"=$(=left(YTDMonth,4))"}>}SaleAmount)

YTDMonth is a variable and it is in the format 201501. I want the expression to evaluate only for 2015 and I wrote the above expression

The problem is that I do not want the above expression to change value with selection of SpendYear. However, it changes values if I select different years in SpendYear filter

In short, I want the above expression to behave like

=sum({<SpendYear={2015}>}SaleAmount) and if someone changes selection, it should not change

How to fix this?

Shah

Re: $ sign issue inside set analysis

Yousef_Amarneh — Fri, 06 Feb 2015 12:50:09 GMT

try this

=sum({<SpendYear={'$(=left(YTDMonth,4))'}>}SaleAmount)

Re: $ sign issue inside set analysis

sunny_talwar — Fri, 06 Feb 2015 12:51:01 GMT

I may be missing something, but whats wrong with using this expression in that case: =sum({<SpendYear={2015}>}SaleAmount)?

Best,

Re: $ sign issue inside set analysis

Yousef_Amarneh — Fri, 06 Feb 2015 12:53:29 GMT

I got what you want exactly

try this

=sum({<SpendYear={'2015*'}>}SaleAmount)

Re: $ sign issue inside set analysis

rubenmarin — Fri, 06 Feb 2015 12:55:48 GMT

Hi Syed, if you want to SpendYear to be always his max value you can use:

=sum({<SpendYear={$(=Max({1} SpendYear))}>}SaleAmount)

In your expression, if there are more than one YTDMonth included in the selections, the Left funcion will return null()

Re: $ sign issue inside set analysis

sunny_talwar — Fri, 06 Feb 2015 12:56:29 GMT

Hi Yousef,

Why would you put an * next to year? Spend year will only be an year (nothing would be attached to it)

Best,

Re: $ sign issue inside set analysis

Yousef_Amarneh — Fri, 06 Feb 2015 12:59:31 GMT

Hi sunidia,

Yes you alright, I thought the spend year format is YYYYMM

Re: $ sign issue inside set analysis

Fri, 06 Feb 2015 13:01:31 GMT

YTDMonth is a variable so it always will have one value. I probably do not need to use a max function here.

I want to do it like Field = {"$(=expression)"} because there are many scenarios where I need the dollar sign expansion in the set analysis. However, I have trouble when the selection changes. I need to fix that

Shah

Re: $ sign issue inside set analysis

Yousef_Amarneh — Fri, 06 Feb 2015 13:04:33 GMT

If you can attached an example with sample of data it would be better, I cannot get your point

Re: $ sign issue inside set analysis

rubenmarin — Fri, 06 Feb 2015 13:12:23 GMT

Ok, I readed it fast, if the values changes when you select another SpendYear, it means than it changes the value of YTDMonth variable.

First you can try what sunindia saids. If fixing set analysis to '2015' works as you expected, the problem is with the value contained in the variable, not in expression.

Re: $ sign issue inside set analysis

sunny_talwar — Fri, 06 Feb 2015 13:12:40 GMT

Did you just say that YTDMonth is a variable? Are you sure it is a variable or you wanted to say its a field name? and can you not use what RubenMarin mentioned in his post?

=sum({<SpendYear={$(=Max({1} SpendYear))}>}SaleAmount)

to me this looks like a sleek solution for what you are trying to achieve, unless SpendYear's Year values doesn't match YTDMonth's Year values.

Best,

Re: $ sign issue inside set analysis

Fri, 06 Feb 2015 13:22:58 GMT

Hi All

Many thanks for the suggestions and the help for this problem. Here are answer to two common questions asked above

1) YTDMonthID is a variable and not a field. It comes from the load script

2) YTDMonthID does not change with selection. However, the expression that I gave above changes its value with different selections. For example, If select any spendyear, the value of the expression changes but the YTDMonthId is always same .i.e 2015

Once again

=sum({<SpendYear={"=$(=left(YTDMonth,4))"}>}SaleAmount)

changes with different spendyear

but

=sum({<SpendYear={2015}>}SaleAmount)

does not change with spendyear

I have also put the left(YTDMonthId,4) in a textbox just to see if the value changes and it does not

Shah

Re: $ sign issue inside set analysis

rubenmarin — Fri, 06 Feb 2015 13:30:29 GMT

Hi, you can set this expression in straight table without caption, this way you can see how QV is translating the $-expansion.

Maybe with

=sum({<SpendYear={"$(=left(YTDMonth,4))"}>}SaleAmount)

=sum({<SpendYear={"$(=left($(YTDMonth),4))"}>}SaleAmount)

Re: $ sign issue inside set analysis

Fri, 06 Feb 2015 13:33:15 GMT

Can you send us what and how the value is being set for Variable: YTDMonth?

The example in the textbox you used is using YTDMonthid and not YTDMonth variable. So can you please share.

If YTDMonth is 201501, then does the SpendYear field also has the similar value in the same format to compare with YTDMonth?

Thanks,

Singh