Variables and IF statements

[tomovangel]

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

))

[prieper]

What is the purpose of your formula?

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

[Anonymous]

Hi,

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

[tomovangel]

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.

[tomovangel]

It is checked

[prieper]

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

[tomovangel]

nope, this gives me all vendors in the chart

[prieper]

with both scripting of the variable?

[heij1421 · Accepted solution]

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

[tomovangel]

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