Solved: Re: qsVariable - Qlik Community

mbjallow6 · ‎2019-07-10

I am trying to use the qsVariable extension in other to allow the user to input (>digits) or l(< digits) then the visualization can be sorted according to the value.

Sum({$<Fact={$(#vSum)}>}Fact)

i use the syntax above to create an app. however, it doesnot recognize the following symbols (< >)

thank you.

Lech_Miszkiewicz · ‎2019-07-11

I don't get why are you using # in your variable

all what you need to do is have expression like:

Sum({$<Fact={"$(vSum)"}>}Fact)

where vSum is your variable input and it can be ">100" or <45 or whatever....

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.

View solution in original post

Lech_Miszkiewicz · ‎2019-07-15

Ok,

My expression is correct. What you have done is you have copied and pasted the exact expression from my post where "" marks were changed to some other characters (note difference between "" and “”)

So once again use this formula from below and it will work!

Sum({<Dim = {"=Sum(Fact)$(vSum)"}>} Fact)

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.

View solution in original post

Lech_Miszkiewicz · ‎2019-07-15

I also would add anther variable which would control sign just to avoid any issues with syntax...

Attached is my sample app.

Hope this helps

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.

View solution in original post

Lech_Miszkiewicz · ‎2019-07-17

Hi,

you need to create another variable as your condition cannot exist in single variable... and embed it in your extended expression.

Expression will look like this (note * union character between sets):

Sum({<Dim = {"=Sum(Fact)>$(vSum)"}*{"=Sum(Fact)<$(vSum2)"}>} Fact)

or when using additional variable for <= sign.....

Sum({<Dim = {"=Sum(Fact)$(vSign)$(vSum)"}*{"=Sum(Fact)$(vSign2)$(vSum2)"}>} Fact)

Regarding your "Last 12 Months" question there are multiple ways of achieving the same:

Users need to apply selections direcly in Qlik Sense
You can harcode Last 12 months in set analysis expression (given that you have properly loaded and formatted date field) like this: Sum({<Date={">=$(=AddMonths(Today()-12))<=$(=Today())"},Dim = {"=Sum(Fact)$(vSign)$(vSum)"}*{"=Sum(Fact)$(vSign2)$(vSum2)"}>} Fact)
You can create Last 12 months flag in script calendar and use flag as selection or in set analysis

Regarding your "Last 12 Months" question....This was very basic question you asked and I really suggest you learn little bit of Qlik Sense first before asking them (go through help.qlik.com (text and videos), go through blogs etc.. or even get a basic 5 day training from Qlik Partner or Qlik) .

I do not want to be harsh but i think there needs to be a minimum level of knowledge when we discuss things here.

Attached is document with all options implemented!

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.

View solution in original post

JordyWegman · ‎2019-07-10

Hi Mbjallow,

I'm having some troubles with your problem. To start, what do you want to achieve? Like, what kind of object are you making?

Jordy

Climber

Work smarter, not harder

mbjallow6 · ‎2019-07-10

hello Jordy

okay let me explain with examples.

i am creating a on time delivery dashboard.

i want the user be able visualize the chat according to the total amount of money spent on each supplier.

therefore i want to create a qsvariable filter where the user can input as follow: (>1000) and will show all suppliers who's transaction is above 1000 USD. or (<1000) and will show all suppliers who's transaction is less than 1000 USD.

as of now i am using a syntax like this "Sum({$<Fact={$(#vSum)}>}Fact)" but with this it can only show suppliers with exactly the amount the user have inputted.

i hope i have explained it better this time around if you still need more details i can share with you.

Lech_Miszkiewicz · ‎2019-07-11

I don't get why are you using # in your variable

all what you need to do is have expression like:

Sum({$<Fact={"$(vSum)"}>}Fact)

where vSum is your variable input and it can be ">100" or <45 or whatever....

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.

mbjallow6 · ‎2019-07-15

the problem of "<" and ">" has been solve but still have issues with the data. to better explain the problem, i will attached a few screenshots

Raw Data

Aggregated data (Sum)

< 100

>100

its like the sum function is not executed.

for example when the user input (< 100) i expect to see only the I dimension to show on the chart because from the sum table it is the only dimension with less that 100.

Thank you in advance.

Lech_Miszkiewicz · ‎2019-07-15

I see,

So this is completly different requirement to what you have descibed as you are not trying to filter Fact field and instead you are trying to filter Dim field based on aggregated expression!!!

In such case your Set Analysis will look completly different and it will be something like this:

Sum({<Dim = {“=Sum(Fact)$(vSum)”}>} Fact)

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.

mbjallow6 · ‎2019-07-15

I have try this set expression: Sum({<Dim = {“=Sum(Fact)$(vSum)”}>} Fact)

but the result is as the screenshot above.

i want to know why do you have to equate the Dim field to the sum of Fact field and the variable?

the Dim field is not numeric and the variable is just equal to the fact field.

I sure that i kinda sound awkward but please be patient with me. I am new here.

Thank you for your time.

Lech_Miszkiewicz · ‎2019-07-15

This is more advanced set analysis syntax and it preaty much means:

show me all Dim's where Sum(Fact) is greater or less Than your variable.

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.

Lech_Miszkiewicz · ‎2019-07-15

i will build POC and test results.. and will get back to you again in few hours...

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.

mbjallow6 · ‎2019-07-15

exactly what i wanted to mention. "show me all Dim's where Sum(Fact) is greater or less Than your variable."

I will wait for your update.

thank you for the clear and concise insight.