Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Partner
Partner

Variables and IF statements

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

))

Screenshot_1.png

1 Solution

Accepted Solutions
Partner
Partner

Re: Variables and IF statements

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

9 Replies
prieper
Honored Contributor II

Re: Variables and IF statements

What is the purpose of your formula?

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

Partner
Partner

Re: Variables and IF statements

Hi,

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

Partner
Partner

Re: Variables and IF statements

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.

Partner
Partner

Re: Variables and IF statements

It is checked

prieper
Honored Contributor II

Re: Variables and IF statements

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

Partner
Partner

Re: Variables and IF statements

nope, this gives me all vendors in the chart

prieper
Honored Contributor II

Re: Variables and IF statements

with both scripting of the variable?

Partner
Partner

Re: Variables and IF statements

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

Partner
Partner

Re: Variables and IF statements

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