topic Re: Variables and IF statements in QlikView

Variables and IF statements

tomovangel — Wed, 25 Nov 2020 16:16:04 GMT

Hello guys, Firstly I apologize, for not being able to provide sample data, because its sensitive, but i have a screenshot
I have a variable, which controls the NetAmount: for example
IF vZweek = 500 it will show me all vendors with NetAmount below the value.

My straight table is working great, but as you can see my bar chart doesn't show all values,
We have values in 2016 month 01, but the bar chart doesn't visualize it.

My dimensions in the bar chart are Month and Year.
My expression is :

if(vZMonth>0 and vZWeek=0,

count(distinct if(aggr(sum(NetAmount_V),Year,Month,Vendor_Name)<vZMonth,aggr(sum(NetAmount_V),Year,Month,Vendor_Name))),

if(vZMonth=0 and vZWeek>0,

count(if(aggr(sum(NetAmount_V),Year,Month,Week,Vendor_Name)<=vZWeek,aggr(sum(NetAmount_V),Vendor_Name)))

))

Re: Variables and IF statements

prieper — Thu, 14 Sep 2017 07:26:32 GMT

What is the purpose of your formula?

Am not sure, whether you may combined different AGGR-sets in one formula.

Re: Variables and IF statements

Anonymous — Thu, 14 Sep 2017 07:29:27 GMT

Hi,

In the Dimensions tab, just check the "Show All Values" checkbox.

Re: Variables and IF statements

tomovangel — Thu, 14 Sep 2017 07:30:13 GMT

My purpose is to count all Vendors, who have higher NetAmount then the variable, for example if my variable is set to 1000 per month, I want my bar chart to count All Vendors who have higher than 1000.

Re: Variables and IF statements

tomovangel — Thu, 14 Sep 2017 07:31:05 GMT

It is checked

Re: Variables and IF statements

prieper — Thu, 14 Sep 2017 08:31:46 GMT

Suggest to go step by step:

COUNT(DISTINCT IF(AGGR(SUM(NetAmount_V), Year, Month, Vendor_Name) > $(vZWeek), Vendor_Name)

should do in the first instance (not 100% sure, if the variable needs to be enclosed by $(..), though)

and check, if it works

Peter

Re: Variables and IF statements

tomovangel — Thu, 14 Sep 2017 09:33:55 GMT

nope, this gives me all vendors in the chart

Re: Variables and IF statements

prieper — Thu, 14 Sep 2017 10:30:07 GMT

with both scripting of the variable?

Re: Variables and IF statements

heij1421 — Thu, 14 Sep 2017 11:19:51 GMT

Hi Angel,

When I look at the formula, you want to count all amounts per year, month, week, vendor LOWER than vZWeek. And if they fulfill this condition, you want to count how many. Why don't you replace the last part of your formula with just '1'?

if(vZMonth=0 and vZWeek>0,

count(if(aggr(sum(NetAmount_V),Year,Month,Week,Vendor_Name)<=vZWeek, 1 )).

If you want to count all vendors with a higher amount, that it should be > instead of <=

Re: Variables and IF statements

tomovangel — Thu, 14 Sep 2017 13:28:25 GMT

Yup, Your solution works
I found out how to make it work with another type of formula, but i will use yours, Thanks