topic Re: Using variables in set analysis in QlikView

Using variables in set analysis

kdmarkee — Thu, 07 Dec 2017 20:08:15 GMT

I have a scenario where I am using the script to generate a lot variables. Below is the resulting text for just a small subset of those variables as seen in the Variable Overview:

--vYearPartCurr

if(_diMonthNum = 1, [Service Year]+1, [Service Year])

--vYearPartPrior

if(_diMonthNum = 1, [Service Year], [Service Year]-1)

--vExpCurr_A

Sum({$<Date={"$(='<'& MakeDate($(=vYearPartCurr), _diMonthNum, 1))"}, GROUP_KEY={$(=vGroup_A)}, Group=>}[Contracted Amount])

--vExpPrior_A

Sum({$<Date={"$(='<'& MakeDate($(=vYearPartPrior), _diMonthNum, 1))"}, GROUP_KEY={$(=vGroup_A)}, [Service Year]={$(=vYearPartPrior)}, Group=>}[Contracted Amount])

vYearPartCurr and vYearPartPrior represent the correct years when I test them in a Text Box object.

The expression vExpCurr_A shows the correct values.

The problem I am having is that I do not get any values for the expression using vExpPrior_A. The expressions are basically the same except this one has an addition filter on [Service Year], and even if I remove that portion from the set analysis, it still is not working.

I just don't understand why the behavior is different between my 2 expressions of vExpCurr_A and vExpPrior_A. I'm hoping someone can see something I don't or perhaps even offer a different/better approach. Thanks.

Re: Using variables in set analysis

sunny_talwar — Thu, 07 Dec 2017 20:45:50 GMT

Have you checked the variable overview to make sure that the expression still looks the same after the reload? The reason I ask this is because $ sign expansion get evaluated in the script and sometimes you would see parts of the expressions missing from your variable.

Re: Using variables in set analysis

kdmarkee — Thu, 07 Dec 2017 21:47:47 GMT

The syntax posted in the thread was actually copied from the Variable Overview. So with that said, even if I just test my working expression (ie, variable vExpCurr_A) with the full syntax which is Sum({$<Date={"$(='<'& MakeDate($(=vYearPartCurr), _diMonthNum, 1))"}, GROUP_KEY={$(=vGroup_A)}, Group=>}[Contracted Amount]), it works.

Now, if I take that same syntax but replace vYearPartCurr with vYearPartPrior, and just test that much of it (without the Service Date filter in the set analysis), that does not work. That baffles me because both vYearPartCurr with vYearPartPrior give me the correct value in a text box object, but I don't understand why set analysis likes it for the vExpCurr_A expression but not vExpPrior_A.

Hopefully that makes sense.

Re: Using variables in set analysis

sunny_talwar — Fri, 08 Dec 2017 01:27:01 GMT

May be try to remove the = sign within the dollar sign expansion:

Sum({$<Date={"$(='<'& MakeDate($(vYearPartCurr), _diMonthNum, 1))"}, GROUP_KEY = {$(=vGroup_A)}, Group=>}[Contracted Amount])

Re: Using variables in set analysis

Anil_Babu_Samineni — Fri, 08 Dec 2017 01:27:47 GMT

So, you are trying with this?

Sum({$<Date={"$(='<'& MakeDate(if(_diMonthNum = 1, [Service Year]+1, [Service Year]), _diMonthNum, 1))"}, GROUP_KEY={$(=vGroup_A)}, Group=>}[Contracted Amount])

Can you try this in Text object and show the result?

MakeDate(if(_diMonthNum = 1, [Service Year]+1, [Service Year]), _diMonthNum, 1)

Re: Using variables in set analysis

kdmarkee — Fri, 08 Dec 2017 16:45:10 GMT

Yes, that is basically what is occurring. And the result I get from the MakeDate statement (my “current date” scenario) is a correct date like this: 1/1/2018. If I run the other one (my “prior date” scenario) , MakeDate(if(_diMonthNum = 1, [Service Year], [Service Year]-1), _diMonthNum, 1), that also gives me a correct date, ie 1/1/2017.

Re: Using variables in set analysis

Anil_Babu_Samineni — Fri, 08 Dec 2017 16:47:47 GMT

What is that Date format in your field?

Re: Using variables in set analysis

kdmarkee — Fri, 08 Dec 2017 16:50:38 GMT

I've tried with and without the "=" and with and without single quotes, but I don't see why I'd have to when I’m running the same expression but with 2 different variables that both seem to provide the correct date.

How is it that I can run this expression with success:

Sum({$<Date={"$(='<'& MakeDate($(=vYearPartCurr), _diMonthNum, 1))"}, GROUP_KEY={$(=vGroup_A)}, Group=>}[Contracted Amount]),

But this one is not successful:

Sum({$<Date={"$(='<'& MakeDate($(=vYearPartPrior), _diMonthNum, 1))"}, GROUP_KEY={$(=vGroup_A)}, Group=>}[Contracted Amount])?

I’m totally stumped.

Re: Using variables in set analysis

sunny_talwar — Fri, 08 Dec 2017 16:58:50 GMT

I see what might be going on... to test my theory, can you select _diMonthNum = 1. When you do this, I expect that the first expression (which worked previously) will not work anymore. and the second expression (with did not work, will start working). Can you check if my theory is right or not?

Re: Using variables in set analysis

kdmarkee — Fri, 08 Dec 2017 17:28:36 GMT

It doesn't matter what _diMonthNum is selected, my current date expr always works and the prior date expr never works. And I doubt it matters but I'll mention that I have a data island with fields _diMonthNum and _diMonthName, where the user selects the _diMonthName (ie, Jan, Feb,) and behind the scenes I am using _diMonthNum to build a date.

Re: Using variables in set analysis

kdmarkee — Fri, 08 Dec 2017 17:33:43 GMT

Not sure I understand the question, so I'm not sure if this answers it. The user selects a Service Year (ie, 2016, 2017) and a Month (ie, Jan, Feb) and I build the date in my variables which is always going to be the first of the month. The Service Year is tied to my data model (star schema) and my Month is a data island not tied to the data model, and used to just build a date.

Re: Using variables in set analysis

sunny_talwar — Fri, 08 Dec 2017 17:36:16 GMT

Okay, is _diMonthName used as a dimension when you check this? Would you be able to share a sample to test it out?

Re: Using variables in set analysis

kdmarkee — Fri, 08 Dec 2017 18:01:23 GMT

I'll dummy up an app with sample data; have personal info due to working with health data. Will respond back when I have that...

Re: Using variables in set analysis

kdmarkee — Fri, 08 Dec 2017 19:28:10 GMT

I decided to try this as well as my expression and I've narrowed it down to the fact that it does not like the -1. So I'll keep playing with this and see if there is another way to accomplish that. Sum({$<Date={"$(='<'& MakeDate(if(_diMonthNum = 1, [Service Year], [Service Year]-1), _diMonthNum, 1))"}, GROUP_KEY={$(=vGroup_A)}, Group=>}[Contracted Amount]).The other expression works because I don’t use that, rather it has a +1, which it doesn’t seem to mind.

Re: Using variables in set analysis

sunny_talwar — Fri, 08 Dec 2017 19:48:38 GMT

That was sort of where I was getting at... try to wrap it around with () and see if it works

Sum({$<Date={"$(='<'& MakeDate(if(_diMonthNum = 1, [Service Year], ([Service Year]-1)), _diMonthNum, 1))"}, GROUP_KEY={$(=vGroup_A)}, Group=>}[Contracted Amount])

Re: Using variables in set analysis

kdmarkee — Fri, 08 Dec 2017 20:45:19 GMT

Here's an application to experiment with. Thanks.

Re: Using variables in set analysis

sunny_talwar — Fri, 08 Dec 2017 20:50:13 GMT

This seems to have worked for me

Sum({$<Date={"$(='<'& MakeDate($(=vYearPartPrior), _diMonthNum, 1))"}, [Service Year]={'$(=$(vYearPartPrior))'}>}[Contracted Amount])

Re: Using variables in set analysis

sunny_talwar — Fri, 08 Dec 2017 20:51:38 GMT

Alternatively, create the variable vYearPartPrior like this

=if(_diMonthNum = 1, [Service Year], ([Service Year]-1))

and then use your current expression as is

Sum({$<Date={"$(='<'& MakeDate($(=vYearPartPrior), _diMonthNum, 1))"}, [Service Year]={'$(=vYearPartPrior)'}>}[Contracted Amount])

Re: Using variables in set analysis

kdmarkee — Fri, 08 Dec 2017 22:22:26 GMT

I still have to do some more testing but this did return results, thanks. I see you changed this portion, [Service Year]={'$(=$(vYearPartPrior))'} . Our app is super over engineered with tons of crazy date stuff, and combine that with all the symbol mumbo jumbo (‘, $, (), =, etc) and this stuff gets overwhelming quickly. Then after some testing of actual values/results on my end I realized I’m using the wrong year here!

So the expression that is actually correct after some spot checking is:

Sum({$<Date={"$(='<'& MakeDate($(=vYearPartPrior), _diMonthNum, 1))"}, [Service Year]={$(=([Service Year])-1)}>}[Contracted Amount])

Thanks again for your help, I very much appreciate it.