QlikView variable with parameters

[JohnSamuel123]

hi experts,

i have a variable that gives me a total count of if there is downtime in my report:

let vDowntime "=count(total {<time_complete*={'0'}>} time_complete)"

so that i can set the colour of my charts to red if vDowntime > 0

however, the above variable calculates total downtime for all my lines. so im trying to figure out a way in my variable to be able to have it distinguish between each line? 

i have a line ID in my dataset so its just a case of getting the variable working correctly i think. my line id field is called "line_id" and it can have 6 possible values, 1 to 6.

is there a way to get my variable to do a distinct count for downtime in each line and not count up the total downtime throughout all my lines?

thanks!

[marcus_sommer]

The bold part is wrong:

count(total {<time_complete*={'0'}>} time_complete)

Here are only native fields allowed - no wildcards and no expressions. Not existing fields are completely ignored and therefore  you get the total result over all available values. 

- Marcus

[JohnSamuel123]

Hi marcus,

thanks for that. yes so i have replaced all null values to have a "0" instead in my script. so like you said, its now pulling in all "0" values within time_complete

are you suggesting that i need to remove the*?  or how would i go about modifying this to only count the total "0" for each line_id? 

thanks

[stevejoyce]

I think that bold part could be ok.  If he's trying to do an intersection of possible values in time_completed with 0.

I'm confused on a couple things.  So time_complete = 0 indicates there was downtime?  Maybe someone else understands more clearly what you want, otherwise if you can post sample data and mockup of what you are trying to do.  

I think...

1) get rid of total

3) do not have your variable start with an = sign (calculates globally)

3) Possibly you want to aggr over line id or is line id a dimension in your object?

4)  this is unclear: distinct count for downtime... distinct count of what? occurrences?  then you can't do a distinct count of time_complete = 0, that will either be 0 or 1.  

This seems the same to me:  a total calculation of at least 1 record = 0 VS at least 1 line id having at least 1 record = 0.  If you are calculating it globally, that's the same thing?

Is what you want:  Set the color of a Line ID, if that line has atleast 1 time_complete = 0?  i'm thinking something like this:  count(distinct {<time_complete*={'0'}>} record_id)

[JohnSamuel123]

hi @stevejoyce  thanks for your response.

to answer your questions

yes so if there is a time_complete record for a line that = "0"(its null but i changed that in the script to = 0), that means that the line is currently down. so if there was a time/date for time_compete instead, that means that the line was down but it finished at the date/time. there would only be a "0" value for when the line is currently down, there wont be multiple "0"s for each line. 

however what im looking to do is create a "live" picture, where the color of the bar chart for each line_id indicates what the state of the line is.

yes the line id is a dimension in my bar chart. so in my expression in my bar chart i am pulling in the max(order_number) to get the most recent unit processed on the line. and i have background colour expression is "if
(vActiveDowntime >0, rgb(254, 92, 90),"

so vActiveDowntime is the variable that we are talking about. 

however the issue is that my variable is not correct in that doesn't take into account each line line_id and just totals the "0"s so if there is one line that is down, all the bars would go red whereas only the line that is down should go red,

is there a way to do this using 1 variable or would i have to have 6 different variables, each having a locked line_id in the set analysis? 

for example you can see that line_id 239 is currently down as the time_complete = 0 and that is the only line that is down:

however my bar chart is showing that all lines are down:

this is obviously because of my expression above with >0 and my variable being > 0, but is there a way to parse it out to each line in an expression or will i have to create a variable for each line?

thanks 

[stevejoyce]

you don't need a seperate variable for each line.

You have 2 problems in your variable looks like.

1) starting with = sign.  this is calculating globally.

2) you are using total with no fields to group by again calculating globally.

if you did not have either of those it would calculate in the context of your dimensions.

You should create a flag in your data model for the "current state" record for each line_id.  something like:

max(order_numer) as order_number

production_line_id

1 as currentState_order_number

group by production_line_id.

Then use currentState_order_number = {1} in your set analysis.

Maybe that's another problem you are having is you are limiting to the most recent record and if that line had a 0 in any point it's also showing as it is offline.

count(distinct {<currentState_order_number = {1}, time_complete*={'0'}>} order_number)

[JohnSamuel123]

hi @stevejoyce so you are suggesting to catch the "0"s in the script.

how would that flag work? apologies i haven't much experience with flags,

i assume if i get the max order_number for each line id, then do a search for any 0's? 

not sure if this makes a difference but the original time_complete field is a date/time field, so when i used the code 

if (isnull([time_complete]),0,[time_complete]) as [time_complete], ts changed it to a number as seen in the earlier screenshot.

thanks

[stevejoyce · Accepted solution]

Yes that's what i'm suggesting.

I wasn't talking about redefining time_complete ( i assumed you had that working correctly).  I'm saying create another field that flags when time_complete is 0 for your most recent day.  But your set analysis would be fine getting the 0's anyway. 

More importantly, flag your most recent day/latest order_number for each line.

//flag the latest order_number for each production_line_id

left join (data):

max(order_numer) as order_number

production_line_id

1 as currentState_order_number

group by production_line_id.