-
1. Re: Select a last record from table Without a filter
Hi Bart,
Would this work ?
if last() = 0 and [OrjstYmd OS10 GRFSYS 5_Waarde] = 0 then "Uit" end
(if it is the last row and it has a value of 0 then "Uit")
Dana
-
2. Re: Select a last record from table Without a filter
Hello Dana,
thanks for your help,
unfortunately it does not work. (cannot mix aggregate and non-aggregate arguments)
I do know understand why my WINDOW_SUM formule does not work.
The formule is applied to the view table in Tableau and not in the source file (the database)
So i need a formule that selects the last record in my source file...
help is appreciated..
Greets
-
3. Re: Select a last record from table Without a filter
Hi Bart,
I'm not sure why the Window_Sum would not work. It would probably help if I could see your workbook and data. (or a mockup with dummy data and a similar issue)
Does it help if you add sum to my calc to make it an aggregate?
if last() = 0 and SUM([OrjstYmd OS10 GRFSYS 5_Waarde]) = 0 then "Uit" end
Would still like to know why your window_sum doesn't work, but I cannot replicate it without more details to play with.
Dana
-
4. Re: Select a last record from table Without a filter
Hello Dana,
It is a difficult case because I use tableau on log data of a technical system. the idea is that a dashboard gives an opinion on the performance of the technsiche installation. But I'll try to explain.
I have a dashboard with a number of KPIs. Each KPI makes use of two measures. 1 = Calculated setpoint. and 2 = actual value. The calculated setpoint value should not deviate far from the actual values. On the basis of the deviation is get a green tick, orjanje mark or red cross displayed on the dashboard.
This was successful.Only what I am trying to build is that if the system is off, the dashboard displays this.
The system on or off is stored in a table: ([OrjstYmd OS10 GRFSYS 5_Waarde]).structure of Table:
System Time Value
9-9-2013 13:28:00 0
9-9-2013 13:36:00 0
9-9-2013 13:44:00 0
9-9-2013 13:52:00 0
9-9-2013 14:00:00 0
9-9-2013 14:08:00 0
9-9-2013 14:16:00 0
9-9-2013 14:24:00 0
9-9-2013 14:32:00 0
9-9-2013 14:40:00 0
9-9-2013 14:48:00 0
9-9-2013 14:56:00 10 = OFF & 1 = ON
What I need is a function that checks the last record of the Table ([OrjstYmd OS10 GRFSYS 5_Waarde]).
IF the last value from table "([OrjstYmd OS10 GRFSYS 5_Waarde])" = 0 THEN "OFF"ELSEIF AVG ([Calculated setpoint])/ AVG( [actual value] ) < 0.75 THEN "Stooklijn fout"
ELSEIF AVG([Calculated setpoint])/ AVG( [actual value] ) > 1.25 THEN "Stooklijn fout"
ELSEIF AVG([Calculated setpoint ])/ AVG( [actual value] ) < 0.85 THEN "Stooklijn aandacht"
ELSEIF AVG([Calculated setpoint])/ AVG( [actual value] ) > 1.15 THEN "Stooklijn aandacht"
ELSE "Stooklijn goed" END
I can not use a filter thats filter by field: TOP 1 BY System Time MAX because that will effect the rest of my IF ELSE calculation...
What I think I need is a Parameter that is ON or OFF. But the parameter needs to be the result of a calculation.
I am running out of Time for today. I will make a pwb by tommorow morning...
Really like to hear from you Dana,
Thanks & Greets Bart
-
5. Re: Select a last record from table Without a filter
Hello Dana,
I have made a .twb that is showing my case. I have also uploaded a Excel sheet.
Your formula : if last() = 0 and SUM([OrjstYmd OS10 GRFSYS 5_Waarde]) = 0 then "Uit" end
no error this time, but it makes a sum of the entire table and NOT from the last record..
I think the problem is that the first line of my formula needs the dimension "exact" & the rest of my calculation needs the dimension "Day"...
Can not use a filter because that will effect one of the two calculations from my formula...
Like to hear from u.
Thanks & Greets Bart
-
6. Re: Select a last record from table Without a filter
Hi Bart,
I'm not sure if I've got the right files?
There is nothing remotely like calculated setpoints or actual values in your sheet or your excel workbook. None of the fields mentioned seem to be there?
There is no reason why day and exact dates cannot be in the same table. Just add it and hide one
Sorry I'm probably not being helpful enough here, but with the data you're showing in the files attached, I cannot see what you mean compared to story above.
-
7. Re: Re: Select a last record from table Without a filter
Dana,
I'm sorry for wasting your time...
I have changed the names and attached it again..
Thanks,
Bart
-
8. Re: Re: Re: Select a last record from table Without a filter
Time played is time not wasted good experience either way. And you seem to always come up with complex yet not annoying puzzles lol.
I had a play and split the whole thing up into different calcs just to be able to see it step by step. If I understand correctly, the day's average value is 230/232 so in total would be 0.99 and should show Stooklijn C. However since the thingy 5_waarde is 0 the whole thing is overruled by the fact that it is Off. Hope I got that right.
Does the attached workbook help?
-
9. Re: Re: Select a last record from table Without a filter
This is really great. It works exactly as I wanted!!!!!!!
This was the last piece of the puzzle for finishing my dashboard
It's true, I come up with questions that are usually not prompted before.
This is probably because I use Tableau to a judgment on the performance of a technical installation. This is a totally different use. The only similarity is: raw data
Usually you're the only one who responds to my questions. Thanks for this.Last question:
1.
Can I remove: "SUM(Calculated setpoint)", "SUM(Actual value)" and "days avg" without any effects to my core calculation("putting it together")? Answer is YES i think, I do not think this effects my calculation("putting it together").
Can you confirm this?
2.
Can I integrate "Days AVG" & "Last row" in "putting it together"?
My entire dashboard is saved by the answer from you to my question.
Thank you again for your time and input!
greets,
Bart -
10. Re: Re: Select a last record from table Without a filter
Yay! Glad it is working. Keep those puzzles coming
Answer 1 - yes you can remove those fields, they're just filling and they're not necessary.
Answer 2 - Not sure... The way I had it set up last, some calcs use system time and others use table down. I tried setting them all to system time, and saw no difference. Real testing will tell though with datasets that should provide the other options (Stooklijn A and B (when it is ON and OFF) and C as well if it is ON). It seemed to work though...
I made it like this:
if (if last() = 0 and sum([OrjstYmd OS10 GRFSYS 5_Waarde])=0 then "Uit" else "Stooklijn" end) = "Uit" then "Uit"
else
if (window_avg(avg([Calculated setpoint]))/window_avg(avg([actual value]))) < 0.9 then "Stooklijn A"
elseif (window_avg(avg([Calculated setpoint]))/window_avg(avg([actual value]))) > 1.1 then "Stooklijn B"
else "Stooklijn C"
end
end
I know it doesn't look entirely sleek, but it seemed safer to take the bits and put them together the same way than re-write the logic. If you have all the test cases though you can shave off the rough edges until it stops working
Enjoy!
-
11. Re: Select a last record from table Without a filter
Dana,
Thanks again! could not do it without you!
After testing, I came to the conclusion that your formula works
But only with the 'Rows': 1.The compound formula & 2.System time.
(If I don't ad the compound formula as a 'Rows' it does not work.)
Thanks Dana,
Greetsss Bart