topic Re: Using a variable properly inside an IF statement that sums in QlikView

Using a variable properly inside an IF statement that sums

mp802377 — Tue, 03 Jan 2017 21:21:43 GMT

I run the data every hour grabbing from multiple data sets and it is of course populating my charts

The data source I am grabbing from tables are populated at different times, based on system performance. If the system is running slow, the data populates slow so on that hour I run it, it may not all be there. If it is running fine, the data is all there.

Because of this the data mwill not have a null value, but a small value and the chart line goes straight down. For this reason, I don't want to plot those missing or low values. I had to create my expression for today() only. I want to see any null values that are not today because if they are null and it is not today that means there is a problem.

This is my formula and works:

=if(Date(today()),Sum({$<-={"<$(=1000)"}>}transactions),sum(transactions))

this works, but for variables, it does not work. I have a lot of charts, most summing a variable. How would I replace transactions with a variable like vConsum.

I tried below and it came back as an error

=if(Date(today()),Sum({$<-={"<$(=1000)"}>}vConsum),sum(vConsum))

How do I get that to work with a variable that was created?

Re: Using a variable properly inside an IF statement that sums

vishsaggi — Tue, 03 Jan 2017 21:31:40 GMT

Try this?

=if(Date(today()),Sum({$<-={"<$(=1000)"}>} $(vConsum)),sum($(vConsum)))

IF not

Can you post your vConsum variable expression what you are using?

Re: Using a variable properly inside an IF statement that sums

mp802377 — Tue, 03 Jan 2017 21:38:17 GMT

The variable I am using is

vConsum = ($(vTrans) * $(vAtin))

vTrans = (sum(tcount/3600))

vAtin = (sum(expend)/sum(tcount))

'tcount' and 'expend' are fields being pulled in from the script editor. I had put the () around the variables thinking that would help.

Re: Using a variable properly inside an IF statement that sums

vishsaggi — Tue, 03 Jan 2017 22:16:32 GMT

Try this?

LET vTrans = '=Sum(tcount)/3600';

LET vAtin = '=Sum(expend)/Sum(tcount)';

Let vConsum = '=(vTrans)/(vAtin)';

Re: Using a variable properly inside an IF statement that sums

mp802377 — Tue, 03 Jan 2017 22:49:01 GMT

I had created those variables above under settings -- > variable overview. So are you saying that my variables I should create in the script editor instead?

I did that. It is the same outcome, does not work.

But for best practice, do I create all variables in Script Editor?

Re: Using a variable properly inside an IF statement that sums

vishsaggi — Tue, 03 Jan 2017 22:50:20 GMT

Yes, try creating your variables in script editor and see. Should not make any difference. However, would it be possible to share a sample app with scrambled data?

Re: Using a variable properly inside an IF statement that sums

mp802377 — Wed, 04 Jan 2017 15:22:14 GMT

Hello, I logged off and after a nights rest, I in the morning I saw my problem. I want to thank you for your help, but I figured it out (YES), so if anyone is having same issue, the expression below worked for me. I was sitting here thinking, isn't the point of $( to sum a variable, so why did I need a sum in front of it. And that was it. I was confusing Qlikview.

=if($(vConsum)>1000 and Date(today()), $(vConsum), Null())

Thank you so much for your help and time. I am trying to learn best practices for Qlikview, the book I read was just a developers book and didn't dive into best practices. It had us create the variables in the Variable Overview. I also want to say how much I appreciate the forum, there is everything in here and I really try to search before I ask a question.