topic Re: qsVariable in App Development

qsVariable

mbjallow6 — Wed, 10 Jul 2019 07:09:52 GMT

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.

Re: qsVariable

JordyWegman — Wed, 10 Jul 2019 11:47:33 GMT

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

Re: qsVariable

mbjallow6 — Thu, 11 Jul 2019 02:21:08 GMT

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.

Re: qsVariable

Lech_Miszkiewicz — Thu, 11 Jul 2019 04:17:41 GMT

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....

Re: qsVariable

mbjallow6 — Mon, 15 Jul 2019 04:28:32 GMT

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.

Re: qsVariable

Lech_Miszkiewicz — Mon, 15 Jul 2019 04:41:24 GMT

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)

Re: qsVariable

mbjallow6 — Mon, 15 Jul 2019 06:13:46 GMT

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.

Re: qsVariable

Lech_Miszkiewicz — Mon, 15 Jul 2019 06:17:29 GMT

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.

Re: qsVariable

Lech_Miszkiewicz — Mon, 15 Jul 2019 06:19:39 GMT

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

Re: qsVariable

mbjallow6 — Mon, 15 Jul 2019 07:16:09 GMT

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.

Re: qsVariable

Lech_Miszkiewicz — Mon, 15 Jul 2019 09:08:24 GMT

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)

Re: qsVariable

Lech_Miszkiewicz — Mon, 15 Jul 2019 09:20:48 GMT

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

Re: qsVariable

mbjallow6 — Mon, 15 Jul 2019 14:43:01 GMT

Thank you so much for the support.

I have finally worked as i wanted.

Re: qsVariable

mbjallow6 — Wed, 17 Jul 2019 07:09:16 GMT

the previous solution have worked just fine. However, i am faced by a more complex requirement.

from the previous solution how can i improve on the syntax in order for the user to be able to enter ">1000 <2000" that is to say greater than 1000 but less than 2000. Furthermore, how can i make sure that only data within the past 12 month is filter.

Thank you in advance.

Re: qsVariable

Lech_Miszkiewicz — Thu, 18 Jul 2019 01:06:17 GMT

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!

Re: qsVariable

mbjallow6 — Fri, 19 Jul 2019 07:03:44 GMT

Thank you.

well regarding the last 12 months question, maybe i have not framed the question well. because already i know how to filter the last 12 months data. the issue i have actually is i am using about 5 measures in one bar chart. and there are times that some of the measures will not have data and the dimension field will still appear without any bar against it.

so what i wanted to achieve is any time that a filter is performed, if a particular measure does not have data, then the rest of the other measure should not appear.

I am sorry for any inconvenience. I am new in qlik but i am determine to learn. i have a qlik continues classroom account also i have subscribe to most qlik channels on youtube.

Re: qsVariable

Lech_Miszkiewicz — Sat, 20 Jul 2019 00:56:52 GMT

Hi,

I see,

this is then different topic and you should open different topic/question for it as on community we try to stick to subjects and dont jump from one to another questions within the same thread.

cheers