Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kdmarkee
Specialist
Specialist

Using variables in set analysis

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.

1 Solution

Accepted Solutions
sunny_talwar

This seems to have worked for me

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

Capture.PNG

View solution in original post

18 Replies
sunny_talwar

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.

kdmarkee
Specialist
Specialist
Author

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.

sunny_talwar

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])

Anil_Babu_Samineni

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)

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
kdmarkee
Specialist
Specialist
Author

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.

Anil_Babu_Samineni

What is that Date format in your field?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
kdmarkee
Specialist
Specialist
Author

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.

sunny_talwar

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?

kdmarkee
Specialist
Specialist
Author

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.