Hello!

I am a bit stucked so maybe you may help 🙂

I have a list of projects A, B, C, D.

Each project has assigned a program value : program X, Y, Z.

Each project has an EAC value.

I would like to create a list of Program/Projects that are greater than a thresold, let's say 400. This is used as a dimension (a dimension for the "bar" in a bar graph).

Example :

• project A : EAC = 350 and program value is "program X"
• project B : EAC = 450 and program value is "program X"
• project C : EAC = 550 and program value is "program Y" or not assigned (value "<NoData>")

In that example, the code would return a list of values : program X (as 800 > 400) and project C (550 > 400). 

My code below is working more or less, however, there is strange behaviors. I have missing values. If I treat that a bit differently, I have other conflict (for example, the list would return "project B" as well, as it is an individual value above 400. Which is not good as this project is already embedded in the "program X" value, also listed...)

Any idea how to treat that in a better way than my code below ?

thanks !

=

if(

(
// test if there is a program assigned or not
[Project Program]<>' <NoData>'

AND

// shows the program names when the total EAC of the program is greater than 400M
aggr(

(
sum({$<[Cost Baseline Version]={"RF*"}
,[Project Tiering]={"Tier 1","Tier 2","Tier 3"},[Monthly Cost Destination]={'CAPEX','OPEX'}
>} EAC/1000000)
)

>400
,[Project Program])
)

,[Project Program],

// else if the program is not above 400M, check and show if any individual project is above 400M

if(

(
[Project Program]=' <NoData>'

AND

aggr(sum({$<[Cost Baseline Version]={"RF*"},[Project Tiering]={"Tier 1","Tier 2","Tier 3"},[Monthly Cost Destination]={'CAPEX','OPEX'}
>} EAC/1000000)>400,[Project Description])
)

,[Project Description]

))

Please tell me how to correctly write the formula for the measure of a diagram:

If the variable “filename” contains “AP”, then calculate the ratio of the number of values “1” for the variable “Code by Classifier” to the number of the sum of values “1” and “0” for the variable “Code by Classifier”

I tried two formulas, but both did not work correctly, it seems to me:

1. If(WildMatch(filename, '*АР*'), 

   Count(If([Code by Classifier] = 1, 1, 0)) / Count(If([Code by Classifier] = 1 or [Code by Classifier] = 0, 1, 0))

)

2. Count({< [Code by Classifier] = {"1"}, [filename]={"*АР*"} >} [Code by Classifier]) / Count({< [Code by Classifier] = {"0", "1"}, [filename]={"*АР*"} >} [Code by Classifier])

Hi , I would like to know if it is possible to have intersection without using P()  .

I used the below calculations and does not give me correct results.

1) Count({<ID=p({<year={'2022'}>})>*p({<year={'2023'}>})>}distinct ID) 

this calculation bumps up numbers since the individual P() also does that.

2) Count({<year={'2022'},ID=P({<year={'2023'}>}ID)>}distinct ID) 

This calculation from other post is working to some extent but here and there bumps up the numbers a little and I saw P() adds up extra ID's in the count .

So I have Count({<year={'2022'}>}distinct ID) and Count({<year={'2023'}>}distinct ID) , which fetch me correct results , is it possible to have a intersection between them?

Thank you!!

Hi,

I try to concatenate these 2 tables and the fields from the budget one (.xlsx) results in nulls as you can see in the image attatched, where I know there are values. Is there something wrong? 

Many thanks!!!

This is the code: 

Hello Qlik community,
I need a field where I can see when a user last used Qlik. Is this field included in my data model or do I need to calculate it myself?

Hi, I am new to qlik,
I have a pivot table with the dimensions group and subgroup.
the group field is a concatenation of group number and the group name (Num_Group & '- '& Group) and the Subgroup field has the same logic
( (Num_Subgroup & '- '& Subgroup)).
The problem is that I get a null concatenation in the filter panel.
in my dimension I was putting this expression: if(isnull(Group_concat or Group_concat='-'), Group_concat), but it still doesn't remove the null concatenation from the filter...
Can you help me, please.

Hi Qlik Community

I'm having some troubles with conditional coloring.

I have a line graph like below with 2 measures. It is the 2 measures in %. 

I need to color each line with a distinct color code and not the default values.

I'm using the color by expression but cant get the desired outcome. I have a flag that should control the color.

When flag_field = 1, color 1 and when flag_field = 0, color 2 should be applied.

Any help is appreciated.

Hi All,

I am trying to get some data out of an database. However, the load script does give an error in Qlik. When I run the SQL statement in the database, there occurs no error.

See below the error and the SQL statement.

Select
a.FK_BUDGETORDERINTAKEYEAR,
a.BUDGET,
a.MARGE,
a.BUDGETPRODUCTION,
a.PK_U_BUDGETORDERCATEGORY,
a.FK_BUDGETORDERCATEGORY,
a.FK_BUDGETSERVICECONTRACTTYPE,
a.INVOICEORDERINTAKE,
b.PK_R_ORDER,
b.FK_ORDERCATEGORY,
c.PK_R_SALESORDERDETAILMISC,
c.NETSALESAMOUNT,
c.ORDERDATE,
//c.FK_SALESPERSON,
d.FK_FINANCIALYEAR,
d.PRODUCTIONPERCENTAGE

FROM U_BUDGETORDERCATEGORY a
LEFT JOIN R_ORDER b ON a.FK_BUDGETORDERCATEGORY = b.FK_ORDERCATEGORY
LEFT JOIN V_R_SALESORDERDETAILMISC c ON b.PK_R_ORDER = c.FK_ORDER
LEFT JOIN U_PRODUCTION d ON c.PK_R_SALESORDERDETAILMISC = d.FK_SALESORDERDETAILMISC;